将两列与一个合并函数进行比较

Question

我尝试了这个查询，但它不起作用，ORA-00904错误。

SELECT 
C.BUSINESS_UNIT 
,COALESCE(L.CHILD_CONTR_ID
,L.PARENT_CONTR_ID) AS CONTRAT 
, C.BILL_TO_CUST_ID
FROM C_LIE_VW L 
,CONTR_HDR C 
WHERE 1=1 
AND CONTRAT=C.CONTRACT_NUM 
AND (L.PARENT_CONTR_ID <> ' ' 
OR L.CHILD_CONTR_ID IS NOT NULL)

看来我做不到：AND CONTRAT=C.CONTRACT_NUM

如何将合并柱与另一列进行比较？

Answer 1

请使用下面的查询，你不能使用别名来连接一个值，而你必须使用完整的表达式。而且您也不需要使用 1=1 条件。

SELECT
C.BUSINESS_UNIT 
,COALESCE(L.CHILD_CONTR_ID
,L.PARENT_CONTR_ID) AS CONTRAT 
, C.BILL_TO_CUST_ID
FROM C_LIE_VW L 
,CONTR_HDR C 
WHERE COALESCE(L.CHILD_CONTR_ID,L.PARENT_CONTR_ID)=C.CONTRACT_NUM 
AND (L.PARENT_CONTR_ID <> '' OR L.CHILD_CONTR_ID IS NOT NULL);

如果需要，下面是联接查询，

SELECT 
C.BUSINESS_UNIT 
,COALESCE(L.CHILD_CONTR_ID
,L.PARENT_CONTR_ID) AS CONTRAT 
, C.BILL_TO_CUST_ID
FROM C_LIE_VW L inner join
CONTR_HDR C 
on (COALESCE(L.CHILD_CONTR_ID,L.PARENT_CONTR_ID)=C.CONTRACT_NUM ) 
WHERE (L.PARENT_CONTR_ID <> ' ' 
OR L.CHILD_CONTR_ID IS NOT NULL);