CODEWITHSUNDEEP
oracle-databaseoracle11gambiguous
shivam
shivam

Reputation: 383

Column ambiguously defined Oracle SQL

I'm facing this ambiguously defined error. I understand this is due to alias not defined properly but I am not able to understand in the below query why this error is coming. I'm using Oracle. 

    SELECT 
papf.person_number as personnumber,
paam.position_id as position,
fabu.bu_name
FROM   
(SELECT 
papf.person_number,
paam.person_id,
fabu.bu_name,
fabu.bu_id,
paam.assignment_id,
paam.assignment_number,
paam.action_code,
paam.effective_start_date,
wft.outcome,
htd.state,
htd.status,
Count(paam.assignment_id)
over (
    PARTITION BY paam.assignment_id) rowcnt
FROM   
per_all_assignments_m paam,
per_all_people_f papf,
hrc_txn_header hth,
hrc_arm_process_vl hapv,
hrc_txn_data htd,
fa_fusion_soainfra.wftask wft,
fun_all_business_units_v fabu
WHERE  
paam.primary_assignment_flag = 'Y'
AND paam.effective_latest_change = 'Y'
AND paam.assignment_status_type = 'ACTIVE'
AND paam.action_code IN ( 'GLB_TRANSFER', 'TRANSFER' )
AND hth.subject = 'PER_ALL_PEOPLE_F'
AND hth.subject_id = paam.person_id
AND hth.object IN ( 'PER_ALL_ASSIGNMENTS_M', 'PER_ALL_PEOPLE_F' )
AND hth.object_id = Decode(hth.object, 'PER_ALL_ASSIGNMENTS_M', paam.assignment_id,
                                    'PER_ALL_PEOPLE_F', paam.person_id)
AND hapv.process_id = hth.process_id
AND hapv.txn_module_identifier IN ( 'Transfers', 'ManageEmployment', 'AddWorkRelationship' )
AND htd.transaction_id = hth.transaction_id
AND wft.identificationkey = To_char(hth.transaction_id)
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paam.person_id = papf.person_id
and  sysdate between fabu.date_from and fabu.date_to
and  fabu.status='A'
and  paam.business_unit_id=fabu.bu_id
GROUP  BY papf.person_number,
paam.person_id,
paam.assignment_id,
fabu.bu_name,
fabu.bu_id,
paam.assignment_number,
paam.action_code,
paam.effective_start_date,
wft.outcome,
htd.state,
htd.status) t1,
per_all_people_f papf,
per_all_assignments_m paam,
fun_all_business_units_v fabu
WHERE  rowcnt = 1
AND outcome = 'APPROVE'
AND state = 'COMPLETE'
AND status = 'APPROVED'
AND paam.person_id = t1.person_id
AND paam.action_code = t1.action_code
AND paam.assignment_status_type = 'INACTIVE'
AND paam.assignment_sequence = 1
AND paam.effective_start_date = t1.effective_start_date
AND paam.assignment_type = 'E'
and papf.person_id = t1.person_id
AND papf.effective_start_date = t1.effective_start_date
AND fabu.bu_id = t1.bu_id

I'm defining the paam table and papf table the same way and not facing issue in them.

Why I am facing this error when I try to add fabu table in end ?

Upvotes: 0

Views: 304

Answers (1)

Ori Marko
Ori Marko

Reputation: 58772

You forgot to add t1. when refer to some columns that can be found in other tables:

WHERE   t1.rowcnt = 1
AND  t1.outcome = 'APPROVE'
AND  t1.state = 'COMPLETE'
AND  t1.status = 'APPROVED'

Upvotes: 2

Related Questions