November 19, 2018 at 9:36 am
I need to check if col1 is null, then show Col2 else show col3.
Used a case statement in the select as below.
Case when Col1 is null
then Col2
else Col3
end as ShowCol.
My DBA thinks using IsNull is simpler.
Any suggestions, if IsNull can be used for this situation.
November 19, 2018 at 9:43 am
s-sql - Monday, November 19, 2018 9:36 AMI need to check if col1 is null, then show Col2 else show col3.
Used a case statement in the select as below.
Case when Col1 is null
then Col2
else Col3
end as ShowCol.My DBA thinks using IsNull is simpler.
Any suggestions, if IsNull can be used for this situation.
No. Your current solution is as good as it gets. Your DBA should stick to taking backups 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 19, 2018 at 9:53 am
I can't see how ISNULL would work here. ISNULL returns the second expression if the first expression has a value of NULL. Considering that the expression you want to return is never the expression you are checking is NULL, ISNULL isn't going to help.
ISNULL in T-SQL isn't the same as the ISNULL operator in some programming language,s which returns a boolean result based on if the value of the expression is NULL or not. Perhaps that's what the DBa think it does in T-SQL; in which case Phil is right, they need to stick to making backups and giving bad advice on how to write T-SQL expressions. :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 19, 2018 at 10:09 am
You can use IIF from SQL 2012 onwards. It does the same as CASE but with a bit less typing:IIF(Col1 IS NULL,Col2,Col3)
November 19, 2018 at 10:25 am
It can be done using ISNULL(). I'm not trying to say that's necessarily better, just that it can be done, assuming col1 <> col2:
SELECT ISNULL(NULLIF(ISNULL(col1, col2), col1), col3)
FROM ( VALUES(1,2,3),(NULL,2,3) ) AS cols(col1,col2,col3)
Edit:
I think this handles the case where col1 = col2, and assuming col2 is not null -- yeah, this is better left to a simple CASE statement:
SELECT ISNULL(NULLIF(ISNULL(ISNULL(col1, col2), col1), col1), col3)
FROM ( VALUES(1,2,3),(NULL,2,3), (4,4,5) ) AS cols(col1,col2,col3)
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".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply