SELECT的情况-未返回预期结果

Question

我正在尝试返回"Y"；如果SC中的ContractID出现在AT_TMP_中；N〃；如果没有。

这是我当前的代码-当前；Y"；无论如何都会返回。

注意：T-SQL我很满意，PL/SQL&Oracle对我来说还是比较新的

WITH AT_TMP_ AS (
SELECT CONTRACT_ID FROM SC_SERVICE_CONTRACT_CFV WHERE 
EXISTS(
SELECT 1 
FROM SC_SRV_CONTRACT_INVPLN_cfv cs 
WHERE SC_SERVICE_CONTRACT_cfv.CONTRACT_ID = cs.contract_id 
AND cs.cf$_invoice_date is null 
AND CS.CF$_INV_NOT_REQ_DB = 'FALSE'
))

SELECT SC.CONTRACT_ID, CASE
WHEN
SC.CONTRACT_ID IS NOT NULL THEN 'Y'
ELSE 'N'
END AS YES_NO
FROM SC_SERVICE_CONTRACT_cfv SC

LEFT OUTER JOIN 
AT_TMP_
ON AT_TMP_.Contract_ID = SC.Contract_ID;

Answer 1

您需要在AT_TMP_中执行该案例。SC永远不会为null，因为它位于左联接的左侧。AT_TMP可以为null，因为它位于左联接的右侧。

WITH AT_TMP_ AS (
SELECT CONTRACT_ID FROM SC_SERVICE_CONTRACT_CFV WHERE 
EXISTS(
SELECT 1 
FROM SC_SRV_CONTRACT_INVPLN_cfv cs 
WHERE SC_SERVICE_CONTRACT_cfv.CONTRACT_ID = cs.contract_id 
AND cs.cf$_invoice_date is null 
AND CS.CF$_INV_NOT_REQ_DB = 'FALSE'
))

SELECT SC.CONTRACT_ID, CASE
WHEN
AT_TMP_.CONTRACT_ID IS NOT NULL THEN 'Y'
ELSE 'N'
END AS YES_NO
FROM SC_SERVICE_CONTRACT_cfv SC

LEFT OUTER JOIN 
AT_TMP_
ON AT_TMP_.Contract_ID = SC.Contract_ID;

Answer 2

您的case表达式目前没有引用at_tmp_；您执行了一个外部联接，但对该联接的结果不执行任何操作。您可以更改case表达式来检查CTE中的值，如@banana_99所示。

但你并不真的需要CTE。您可以直接使用exists检查：

SELECT SC.CONTRACT_ID,
CASE
WHEN EXISTS (
SELECT null
FROM SC_SRV_CONTRACT_INVPLN_cfv SCI 
WHERE SCI.CONTRACT_ID = SC.contract_id 
AND SCI.cf$_invoice_date is null 
AND SCI.CF$_INV_NOT_REQ_DB = 'FALSE'
)
THEN 'Y' ELSE 'N' END AS YES_NO
FROM SC_SERVICE_CONTRACT_cfv SC;

Answer 3

我认为您需要EXISTS而不是LEFT JOIN-

WITH AT_TMP_ AS ( SELECT CONTRACT_ID
FROM SC_SERVICE_CONTRACT_CFV
WHERE EXISTS(SELECT 1 
FROM SC_SRV_CONTRACT_INVPLN_cfv cs 
WHERE cs.CONTRACT_ID = cs.contract_id 
AND cs.cf$_invoice_date is null 
AND CS.CF$_INV_NOT_REQ_DB = 'FALSE')
)
SELECT SC.CONTRACT_ID,
CASE WHEN EXISTS (SELECT 1 FROM AT_TMP_
WHERE AT_TMP_.Contract_ID = SC.Contract_ID) THEN 'Y'
ELSE 'N' END AS YES_NO
FROM SC_SERVICE_CONTRACT_cfv SC;