CODEWITHSUNDEEP
sqloracle-databasecase
AdzzzUK
AdzzzUK

Reputation: 298

Case with SELECT - not returning expected results

I am trying to return "Y" if the ContractID in SC appears in AT_TMP_, and "N" if it does not.

Here is my current code - currently, "Y" is returned regardless.

Note: T-SQL I'm fine with, PL/SQL & Oracle is still relatively new to me

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;

Upvotes: 0

Views: 82

Answers (3)

banana_99
banana_99

Reputation: 641

You need to do the case in AT_TMP_. SC is never going to be null because it is on the left side of the left join. AT_TMP can be null as it's on the right side of the 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 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;

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I think you need EXISTS instead of 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;

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

Your case expression isn't referring to at_tmp_ at the moment; you do an outer join but then don't do anything with the result of that join. You can change the case expression to check the value from the CTE instead, as @banana_99 showed.

But you don't really need the CTE. You can use the exists check directly:

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;

Upvotes: 1

Related Questions