All Forums Database
diw07 22 posts Joined 04/08
18 Nov 2008
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",casewhen 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"

Fred 1096 posts Joined 08/04
18 Nov 2008

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 ...

diw07 22 posts Joined 04/08
18 Nov 2008

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... ORCASE WHEN c.c_pym_det = 'FAC' THEN 'Assisted Living - NH'CASE WHEN c.c_pym_det = 'PHC' THEN 'Assisted Living - HC'??THANKS!!!

Fred 1096 posts Joined 08/04
19 Nov 2008

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_detWHEN 'FAC' THEN 'Assisted Living - NH'WHEN 'PHC' THEN 'Assisted Living - HC'else 'UNKNOWN'END)

You must sign in to leave a comment.