CASE Statements

Question

CASE Statements

wpotter

SSC Veteran

Points: 222
More actions
March 4, 2008 at 5:14 am

#184718

Good morning everyone;
Is it possible to call a table in a CASE statement in the else area:
(CASE --(CASE 1)
WHEN cd.location_type_id != 1 --Home
THEN f.county_id
WHEN cd.location_type_id != 4 --Day Care
THEN f.county_id
WHEN cd.location_type_id != 5 --Assisted Living Facility
THEN f.county_id
WHEN cd.location_type_id != 6 --Other
THEN f.county_id
ELSE cd.county_id
END) 'County of Location',
The else here in this CASE statement form some reason does not pull data from this table.
I do thnak you for your help. Please don't flame me too much if this is an easy fix I am sill an entry level reporting Analyst.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

wpotter (3/4/2008)
Good morning everyone;
Is it possible to call a table in a CASE statement in the else area:
(CASE --(CASE 1)
WHEN cd.location_type_id != 1 --Home
THEN f.county_id
WHEN cd.location_type_id != 4 --Day Care
THEN f.county_id
WHEN cd.location_type_id != 5 --Assisted Living Facility
THEN f.county_id
WHEN cd.location_type_id != 6 --Other
THEN f.county_id
ELSE cd.county_id
END) 'County of Location',
The else here in this CASE statement form some reason does not pull data from this table.
I do thnak you for your help. Please don't flame me too much if this is an easy fix I am sill an entry level reporting Analyst.

Sorry, the first thing that is really strange, are you sure you want to use != instead of = in your conditions?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

wpotter SSC Veteran Points: 222 More actions · Answer 2

yes. This not equal too came from requirements from a client.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

With you're unequal comparison the last 3 options will never be reached.

So it also won't go to the ELSE because any value will either be unequal to 1 or 4.

[font="Verdana"]Markus Bohse[/font]

wpotter SSC Veteran Points: 222 More actions · Answer 4

Oh I never thought of that, good point. How should I fix that?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

Why does it have to be not equal too? This goes against the purpose of the case statement. Case statements should evaluate to one option, not a multitude of them. In your case, !1 will be 2,3,4,5,6. !4 will be 1. The case statement should be used as follows:

Case when location_type = 1... then do something... when location_type = 2... then do something etc.

I see your only options being add more conditions or use the = operator.

My blog: http://jahaines.blogspot.com