CASE Statement error please help

Database
Enthusiast

CASE Statement error please help

Hi All,

While running the below query it gives me error ' Illegal expression in WHEN clause of CASE expression' . Can you please tell me what is wrong with the below code.

SEL A.*,

CASE WHEN CUST_NM = CC_Lookup and CUST_NM is not null then 

          CASE WHEN CUST_NM2 in (sel CC_LOOKUP from ud804.Process_Run_1) and CUST_NM2 is not null THEN 

                       CASE WHEN CUST_NM3 in (sel CC_LOOKUP from ud804.Process_Run_1) and CUST_NM3 is not null THEN  Receiver_Brch_DID 

Else CUST_NM3 END

ELSE CUST_NM2 END

WHEN CUST_NM is null then Receiver_Brch_DID 

Else CUST_NM 

END AS CC_NEW

from TABLE.SPACE1234 A

3 REPLIES
Enthusiast

Re: CASE Statement error please help

help please

Enthusiast

Re: CASE Statement error please help

Can someone help please?

Senior Apprentice

Re: CASE Statement error please help

You need to rewrite the IN to a Correlated Subquery. And you can remove the IS NOT NULL, because NULs never compare equal:

SEL A.*,
CASE WHEN CUST_NM = CC_Lookup
THEN CASE WHEN CUST_NM2 = (SEL CC_LOOKUP FROM ud804.Process_Run_1 AS b
WHERE a.CUST_NM2 = b.CC_LOOKUP)
THEN CASE WHEN CUST_NM3 =(SEL CC_LOOKUP FROM ud804.Process_Run_1 AS b
WHERE a.CUST_NM3 = b.CC_LOOKUP)
THEN Receiver_Brch_DID
ELSE CUST_NM3
END
ELSE CUST_NM2
END
WHEN CUST_NM IS NULL THEN Receiver_Brch_DID
ELSE CUST_NM
END AS CC_NEW
FROM TABLE1.SPACE1234 A

Additionally I would try to avoid any kind of Scalar Subquery and replace it with an Outer Join. This should return the same result:

SEL A.*,
CASE WHEN CUST_NM = CC_Lookup
THEN CASE WHEN CUST_NM2 = b.CC_LOOKUP
THEN CASE WHEN CUST_NM3 = c.CC_LOOKUP
THEN Receiver_Brch_DID
ELSE CUST_NM3
END
ELSE CUST_NM2
END
WHEN CUST_NM IS NULL THEN Receiver_Brch_DID
ELSE CUST_NM
END AS CC_NEW
FROM TABLE1.SPACE1234 A
LEFT JOIN ud804.Process_Run_1 AS b
ON a.CUST_NM2 = b.CC_LOOKUP
LEFT JOIN ud804.Process_Run_1 AS c
ON a.CUST_NM3 = c.CC_LOOKUP