Having trouble with CASE command

[gjmac2]

i am trying to populate a column based on certain criteria using a CASE command, and I am getting "ADO error: Invalid syntax near the keyword 'AS'.

Here is the code:

CASE WHEN DBO.VW_RPTELIGIBLEORGANDONOR.REFERRALTYPE = 'REGULARREFERRAL' AND
dbo.VW_REferralActivity.organdisp = 'Family Declined' OR
dbo.VW_REferralActivity.organdisp = 'KNOWN PRIOR OBJECTION' THEN 'ED' else
CASE WHEN DBO.VW_RPTPotentialORGANDONOR.REFERRALTYPE = 'REGULARREFERRAL' AND
dbo.VW_REferralActivity.organdisp <> 'Family Declined' OR
dbo.VW_REferralActivity.organdisp <> 'KNOWN PRIOR OBJECTION' or
dbo.VW_REferralActivity.organdisp <> 'recovered' THEN 'PD' else null END AS donortype,

Anyone have an idea what I'm doing wrong?

 

[SQLDenis]

you need a second END after the first END

Both CASE statements have to be ended

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Jamfool]

first glance Id say you have two case statements and only one end.

 

[hmckillop]

you cant have an embedded case i.e. you need to have an END with every CASE Statement

e.g.

CASE WHEN 
	DBO.VW_RPTELIGIBLEORGANDONOR.REFERRALTYPE = 'REGULARREFERRAL' 
		AND (dbo.VW_REferralActivity.organdisp = 'Family Declined' 
			OR dbo.VW_REferralActivity.organdisp = 'KNOWN PRIOR OBJECTION') THEN 'ED' 
	WHEN DBO.VW_RPTPotentialORGANDONOR.REFERRALTYPE = 'REGULARREFERRAL' AND 
                      (dbo.VW_REferralActivity.organdisp <> 'Family Declined' OR
                      dbo.VW_REferralActivity.organdisp <> 'KNOWN PRIOR OBJECTION' or
                      dbo.VW_REferralActivity.organdisp <> 'recovered') THEN 'PD' 
	else null END AS donortype,

"I'm living so far beyond my income that we may almost be said to be living apart

 

[unclerico]

try this:

CASE WHEN DBO.VW_RPTELIGIBLEORGANDONOR.REFERRALTYPE = 'REGULARREFERRAL' AND dbo.VW_REferralActivity.organdisp = 'Family Declined' OR dbo.VW_REferralActivity.organdisp = 'KNOWN PRIOR OBJECTION' THEN 'ED' 
     WHEN DBO.VW_RPTPotentialORGANDONOR.REFERRALTYPE = 'REGULARREFERRAL' AND dbo.VW_REferralActivity.organdisp <> 'Family Declined' OR dbo.VW_REferralActivity.organdisp <> 'KNOWN PRIOR OBJECTION' or dbo.VW_REferralActivity.organdisp <> 'recovered' THEN 'PD' 
     else null END AS donortype

 

[unclerico]

wow, quite a response in a very short period of time

 

[gjmac2]

Thank you all very much. It works now. I took out the second CASE command, and it works.

Thanks again.

 

[mutley1]

Shouldn't you group the 2 OR clauses as well otherwise it'll pick the sceond one up regardless of REFERRALTYPE

CASE WHEN DBO.VW_RPTELIGIBLEORGANDONOR.REFERRALTYPE = 'REGULARREFERRAL' AND 
                      [COLOR=red]([/color]dbo.VW_REferralActivity.organdisp = 'Family Declined' OR
                      dbo.VW_REferralActivity.organdisp = 'KNOWN PRIOR OBJECTION'[COLOR=red])[/color] THEN 'ED' else

and the same for the OR in the second half.

M.

 

[mutley1]

Sorry - just read back and realised hmckillop raised the above in his code. Thank god it's a bank holiday weekend here in the UK!