时间戳

首页 > 解决方案 > 比较两行中的值并得到 ORA-00918: 列定义不明确

sql - 比较两行中的值并得到 ORA-00918: 列定义不明确

问题描述

我正在尝试使用 CASE 语句比较两个不同行中一列的值。内部 SELECT 语句是相同的，并且工作正常，为我提供了我需要的字段。然后我在其中一个关键字段（KYCID）上加入了他们。这就是我得到“列不明确定义”错误的地方。我尝试使用 DISTINCT 删除重复的行 - 仍然是同样的错误。

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';

标签： sqloracleoracle11gnestedora-00918

解决方案

正如通过消除过程确定的那样，您在SELECT查询中引用了相同的命名列。为避免这种名称冲突，请考虑为这些特定列设置别名。

此外，为避免重复，请考虑使用 CTE viaWITH并避免使用 (a, b, c) 或 (t1, t2, t3) 等表别名。最后，在合并所有数据源之前将WHERE条件移至ON过滤器。

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

推荐阅读