Case Statement Help

Question

Post reply

Case Statement Help

don.hughesjr

Old Hand

Points: 349
More actions
September 6, 2016 at 11:45 am

#400684

First of all let me start by saying Hello everyone and I'm a decent Front End Dev not a backend. So my question is am I using the correct method to get convert my data or should I be going in a totally different direction? I hope that I've explained well enough to not confuse anyone.
Update Staging
Set P_Staged_By = CASE
WHEN P_Staged_By = '0' THEN '00'
WHEN P_Staged_By = '1' THEN '10'
WHEN P_Staged_By = '2' THEN '14'
WHEN P_Staged_By = '3' THEN '15'
WHEN P_Staged_By = '4' THEN '10'
WHEN P_Staged_By = '5' THEN '20'
WHEN P_Staged_By = '6' THEN '30'
WHEN P_Staged_By = '7' THEN '50'
WHEN P_Staged_By = '8' THEN '88'
WHEN P_Staged_By = '9' AND Pt is null AND Pn is null AND Pm is null AND P_Ajcc_Grp is null or Pt = 'X' AND Pn = 'X' AND Pm = 'X' AND P_Ajcc_Grp = '99'
THEN '00'
WHEN P_Staged_By = '9' OR Pt = '88' OR Pn = '88' OR Pm = '88' OR P_Ajcc_Grp = '88' THEN '88'
WHEN P_Staged_By = '9' THEN '99'
END
From Staging
GO
The 0-8 methods works flawlessly. The part I'm having trouble on is the P_Staged_By = '9'
WHEN P_Staged_By = '9' AND Pt is null AND Pn is null AND Pm is null AND P_Ajcc_Grp is null this part works fine but when I try to replace the null with 'X' that doesn't work.
WHEN P_Staged_By = '9' OR Pt = '88' OR Pn = '88' OR Pm = '88' OR P_Ajcc_Grp = '88' THEN '88' seems to also work fine.
WHEN P_Staged_By = '9' THEN '99' this will make everything that is 9 into 99.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Can you please post the DDL (Create table), sample data as an insert statement and the expected results?

😎

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I believe that you're missing some parenthesis and you might need to change one of the conditions to allow the last part to be evaluated.

Update Staging

Set P_Staged_By = CASE

WHEN P_Staged_By = '0' THEN '00'

WHEN P_Staged_By = '1' THEN '10'

WHEN P_Staged_By = '2' THEN '14'

WHEN P_Staged_By = '3' THEN '15'

WHEN P_Staged_By = '4' THEN '10'

WHEN P_Staged_By = '5' THEN '20'

WHEN P_Staged_By = '6' THEN '30'

WHEN P_Staged_By = '7' THEN '50'

WHEN P_Staged_By = '8' THEN '88'

WHEN P_Staged_By = '9'

AND ((Pt is null

AND Pn is null

AND Pm is null

AND P_Ajcc_Grp is null)

or (Pt = 'X'

AND Pn = 'X'

AND Pm = 'X'

AND P_Ajcc_Grp = '99')) THEN '00'

WHEN P_Staged_By = '9'

AND( Pt = '88'

OR Pn = '88'

OR Pm = '88'

OR P_Ajcc_Grp = '88') THEN '88'

WHEN P_Staged_By = '9' THEN '99'

END

From Staging;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76989 More actions · Answer 3

If you're not sure, instead of using an "OR", add another CASE. This will both make it easier to understand your logic and prevent problems with not specifying the correct precedence of the operations.

You might also be able to rearrange your CASE expressions to simplify the remaining CASE expressions.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

don.hughesjr Old Hand Points: 349 More actions · Answer 4

Thanks man that did the trick. I just wasn't seeing what I was doing wrong.