Error with Case statement - expected something between the ‘end’ keyword and “)”

Database
N/A

Error with Case statement - expected something between the ‘end’ keyword and “)”

I have the following code and I am almost certain that I'm getting the error due to the fact that it is a nested sub query. Is there any way to work around this? Any help would be greatly appreciated. I'm relatively new to TD!

SELECT b.i_sys_clm AS "SorceClaimID"

,a.c_pol AS "SorcePolicyID"

,c.i_sys_pym_det AS "SorceClaimPaymentID"

,case

when c.c_loc = '99' then null

when c.c_loc = '01' then 'HOSPITAL'
<--------Next Case Statement Throwing the error
when c.c_loc = '02' then(case pyd.c_pym_det

when c.c_pym_det = 'FAC' then 'ASSISTED LIVING - NH'

when c.c_pym_det = 'PHC' then 'ASSISTED LIVING - HC'

when c.c_pym_det= 'THC' then 'ASSISTED LIVING - HC'

else 'UNKNOWN'

end)
------------------------------------------------------------------------------------->
when c.c_loc = '03' then 'HOME CARE - THC'

when c.c_pym_det = 'IHC' then 'HOME CARE - THC'

when c.c_pym_det = 'THC' then 'HOME CARE - THC'

when c.c_loc = '04' then 'NURSING HOME'

when c.c_pym_det = 'FAC' then 'NURSING HOME'

else 'Error'

end) as "ClaimLocName"
3 REPLIES
Teradata Employee

Re: Error with Case statement - expected something between the ‘end’ keyword and “)”

The error message could be better... Your outer parentheses are not balanced, plus you have invalid syntax for the inner CASE:
... case pyd.c_pym_det when c.c_pym_det = 'FAC' then ...

You can either specify an expression between CASE and the first WHEN, and imply an "=" comparison with the value of the expression specified after each WHEN:
e.g. ... case pyd.c_pym_det when 'FAC' then ...
or have the first WHEN immediately follow the CASE and explicitly code a logical expression after each WHEN:
e.g. ... case when c.c_pym_det = 'FAC' then ...
N/A

Re: Error with Case statement - expected something between the ‘end’ keyword and “)”

so something like

(CASE WHEN c.c_pym_det = 'FAC' THEN 'Assisted Living - NH'
WHEN c.c_pym_det = 'PHC' THEN 'Assisted Living - HC'
else 'UNKNOWN'
END)

etc... OR

CASE WHEN c.c_pym_det = 'FAC' THEN 'Assisted Living - NH'
CASE WHEN c.c_pym_det = 'PHC' THEN 'Assisted Living - HC'

??

THANKS!!!
Teradata Employee

Re: Error with Case statement - expected something between the ‘end’ keyword and “)”

Your first example should be OK (assuming you also balance the outer parentheses):

(CASE
WHEN c.c_pym_det = 'FAC' THEN 'Assisted Living - NH'
WHEN c.c_pym_det = 'PHC' THEN 'Assisted Living - HC'
else 'UNKNOWN'
END)

But the other option would be something like this:

(CASE c.c_pym_det
WHEN 'FAC' THEN 'Assisted Living - NH'
WHEN 'PHC' THEN 'Assisted Living - HC'
else 'UNKNOWN'
END)