March 4, 2008 at 5:14 am
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.
March 4, 2008 at 5:26 am
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
March 4, 2008 at 5:28 am
yes. This not equal too came from requirements from a client.
March 4, 2008 at 5:38 am
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]
March 4, 2008 at 5:40 am
Oh I never thought of that, good point. How should I fix that?
March 4, 2008 at 7:00 am
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.
March 4, 2008 at 7:06 am
I do thank you I will try that.
March 4, 2008 at 9:07 am
I want to thank you all for your help. You all were right the logic from the client was wrong. I got the code working. Thank you again.
March 4, 2008 at 9:17 am
NP, I am glad we got everything cleared up 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply