Comparing values in two rows and getting ORA-00918: column ambiguously defined

Question

I am trying to compare values from one column in two different rows using CASE statement. Inner SELECT statements are identical and working OK giving me fields that I need. Then I inner joined them on one of the key fields (KYCID). That's where I get "column ambiguously defined" error. I tried to remove duplicate rows by using DISTINCT - still the same error.

SELECT DISTINCT e.KYCID, 
  CASE WHEN e.HRAC_FLAG <> f.HRAC_FLAG THEN 'FALSE' ELSE 'TRUE' END AS FLAG_COMPARISON
    FROM
     (SELECT gt.task_id, gt.work_item_status, gt.work_item_type, gt.component_id, xref.HRAC_FLAG, 
     xref.case_nbr, xref.task_id, d.kycid, d.core_component_state  
        FROM kyc_gbl_main.global_task gt
          inner join KYC_RGN_NAM_MAIN.CASE_HRAC_XREF xref on gt.component_id = xref.component_id
          inner join kyc_rgn_nam_main.account a on xref.accountid = a.accountid
          inner join kyc_rgn_nam_main.country_appx_account_xref b on  a.accountid = b.accountid 
          inner join kyc_rgn_nam_main.country_appx c on c.cntry_appx_id = b.cntry_appx_id and 
             c.country_appx_state = b.country_appx_state 
          inner join kyc_rgn_nam_main.kyc_main d on d.kycid = c.kycid 
             where gt.work_item_type = 'HRAC_OVERLAY'
             and gt.work_item_status = 'Completed') e

INNER JOIN

        (select gt.task_id, gt.work_item_status, gt.work_item_type, gt.component_id, xref.HRAC_FLAG, 
        xref.case_nbr, xref.task_id, d.kycid, d.core_component_state  
           from kyc_gbl_main.global_task gt
              inner join KYC_RGN_NAM_MAIN.CASE_HRAC_XREF xref on gt.component_id = xref.component_id
              inner join kyc_rgn_nam_main.account a on xref.accountid = a.accountid
              inner join kyc_rgn_nam_main.country_appx_account_xref b on  a.accountid = b.accountid 
              inner join kyc_rgn_nam_main.country_appx c on c.cntry_appx_id = b.cntry_appx_id and 
              c.country_appx_state = b.country_appx_state 
              inner join kyc_rgn_nam_main.kyc_main d on d.kycid = c.kycid   
                where gt.work_item_type = 'HRAC_OVERLAY'
                and gt.work_item_status = 'Completed') f

ON e.KYCID = f.KYCID
  WHERE e.core_component_state ='ACTIVE'
  AND f.core_component_state = 'IN_PROGRESS';

Please tag RDBMS. Oracle? Also, are you sure this *exact* query raises that error? Check carefully against actual query if you posted with changes. Error would raise if using `*`. Immediately, by syntax alone I cannot see why this posted SQL errs out especially since you prefix every column with table alias. Otherwise, try removing `SELECT` columns and expressions one by one, `WHERE` conditions one by one to isolate problem area. — Parfait, Nov 25 '20 at 18:33
Thanks, Parfait. This is Oracle RDBMS. It is the exact query, every column is prefixed as you pointed out. Will try to further debug by removing SELECT columns from the inner query one by one. — StephenV, Nov 25 '20 at 18:56
Yes! In the inner SELECT statement, two columns from different tables had the same name (gt.task_id and xref.task_id). Once the first one was removed, the query started running. Thanks again! — StephenV, Nov 25 '20 at 19:54

score 1 · Answer 1 · answered Nov 25 '20 at 21:19

As determined with process of elimination, you reference the same named column in a SELECT query. To avoid this name collision, consider aliasing those particular columns.

Additionally, to avoid repetition, consider using a CTE via WITH and avoid using table aliases like (a, b, c) or (t1, t2, t3). Finally, move WHERE conditions to ON to filter before combining all data sources.

WITH sub AS (
    SELECT   gt.task_id AS gt_task_id             -- RENAMED TO AVOID COLLISION
           , gt.work_item_status
           , gt.work_item_type
           , gt.component_id
           , xref.HRAC_FLAG
           , xref.case_nbr
           , xref.task_id AS x_ref_task_id        -- RENAMED TO AVOID COLLISION
           , k.kycid
           , k.core_component_state  

    FROM kyc_gbl_main.global_task gt
    INNER JOIN KYC_RGN_NAM_MAIN.CASE_HRAC_XREF xref  
        ON  gt.component_id = xref.component_id
        AND gt.work_item_type = 'HRAC_OVERLAY'    -- MOVED FROM WHERE 
        AND gt.work_item_status = 'Completed'     -- MOVED FROM WHERE 
    INNER JOIN kyc_rgn_nam_main.account acc 
        ON xref.accountid = acc.accountid
    INNER JOIN kyc_rgn_nam_main.country_appx_account_xref cax 
        ON acc.accountid = cax.accountid 
    INNER JOIN kyc_rgn_nam_main.country_appx ca
        ON  ca.cntry_appx_id = cax.cntry_appx_id 
        AND ca.country_appx_state = cax.country_appx_state 
    INNER JOIN kyc_rgn_nam_main.kyc_main k
        ON k.kycid = ca.kycid   
)

SELECT DISTINCT 
           e.KYCID
         , CASE 
              WHEN e.HRAC_FLAG <> f.HRAC_FLAG 
              THEN 'FALSE' 
              ELSE 'TRUE'
           END AS FLAG_COMPARISON
FROM sub e
INNER JOIN sub f 
  ON  e.KYCID = f.KYCID
  AND e.core_component_state = 'ACTIVE'           -- MOVED FROM WHERE  
  AND f.core_component_state = 'IN_PROGRESS'      -- MOVED FROM WHERE

Comparing values in two rows and getting ORA-00918: column ambiguously defined

1 Answers1