Proper CASE statement
-
Hello everyone,
I have a situation where I am trying to organize 2 case statement into 1. There are 3 fields
I literally see that sometimes I have values in all 3 defined dates and the case still return NULLs.
Those are 2 versions that I have and none of them returns it correctly
CASE WHEN fa.S1SDAT IS NULL THEN ar2.S1SDAT when ar2.S1SDAT IS NULL THEN ar.S1SDAT when ar.S1SDAT IS NULL THEN fa.S1SDAT end as [S1SDAT(CASE)]
,CASE WHEN fa.S1SDAT IS NULL THEN ar.S1SDAT when ar.S1SDAT IS NULL THEN ar2.S1SDAT when ar.S1SDAT IS NULL THEN fa.S1SDAT end as [S1SDAT2(CASE)]
-
Use COALESCE instead:
COALESCE(fa.S1SDAT, ar2.S1ADAT, ar.S1SDATE)
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Are you trying to return the first non-null value?
COALESCE()?
-
You do realize that your graphic has the S1SDat column listed 3 times, right? Is there a similar problem with the rest of the code that you've not displayed?
Also, do yourself a favor... "Read'n'Heed" the article at the first link in my signature line below. It'll help a lot with you getting better answers more quickly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
ScottPletcher and pietlinden this is what I need. Thank you very much!!
Jeff Moden I did it 3 times to show that I visually display the result of each of them. Thank you as well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply