4

I am using a CASE expression in my WHERE clause like this:

SELECT *    
FROM    ASPECT.WR_AM_ADT_SUMM
INNER JOIN ASPECT.WR_AM_DLR_DTL
  ON    AS_CNTRY_CD = DD_CNTRY_CD AND 
        AS_DLR_CD = DD_DLR_CD AND 
        AS_YEAR = DD_YEAR
RIGHT JOIN ASPECT.DEALER_MASTER
  ON    COUNTRY_CD = AS_CNTRY_CD AND 
        DEALER_CDE_VEGA = AS_DLR_CD
WHERE
        COUNTRY_CD = '81930' AND 
        LANG_CD = '02' AND 
        (CASE PARM_ADTR_ID 
           WHEN 'ALL' THEN (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') 
           ELSE AS_ADTR_ID_P LIKE LOC_ADTR_ID 
        END) AND 
        DEALER_CDE_VEGA LIKE '8%'
        ;

In the above query when I pass 'ALL' in PARM_ADTR_ID input parameter then first condition should work, otherwise the second condition should work.

As of now it's giving the syntax error, could anyone please tell me how to do this?

  • 1
    What's the point of (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%')? It should be always TRUE. – ypercubeᵀᴹ Feb 11 '16 at 13:35
5

You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might look something like this, if one were to blindly translate your code:

WHERE
    COUNTRY_CD = '81930' AND 
    LANG_CD = '02' AND (
      ( PARM_ADTR_ID = 'ALL'  
        AND (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') ) 
       OR
      ( (PARM_ADTR_ID != 'ALL' OR PARM_ADTR_ID IS NULL)  
        AND AS_ADTR_ID_P LIKE LOC_ADTR_ID )
    ) AND 
    DEALER_CDE_VEGA LIKE '8%'

However, if you remove the redundant conditions it can be simplified like so (HT to ypercubeᵀᴹ, who wakes earlier:)

WHERE
    COUNTRY_CD = '81930' AND 
    LANG_CD = '02' AND (
      PARM_ADTR_ID = 'ALL' OR AS_ADTR_ID_P LIKE LOC_ADTR_ID
    ) AND 
    DEALER_CDE_VEGA LIKE '8%'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.