CODEWITHSUNDEEP
sql-server-2008
Amarundo
Amarundo

Reputation: 2397

SELECT statement needs to output value conditional on NULL data

It was hard to condense in the title what I need... Here it goes. This is for SQL Server 2008.

For a reason that is too long to explain here, I need to have a SELECT statements that will display, among other columns, a value of 1 if 4 columns have a value null. Something like this.

SELECT name, lastname, p1, p2, p3, p4, "1 if p1,p2,p3 and p4 are all NULL, 0 otherwise" city, st, zip
FROM mytable

The questions is how to I translate to code this: "1 if p1,p2,p3 and p4 are all NULL, 0 otherwise" ?

I tried 

CASE len(p1+p2+p3+p4) WHEN NULL 1 ELSE 0 END

But it didn't work. even though when I do

SELECT len(p1+p2+p3+p4) FROM mytable

The result iS NULL.

I tried IF ELSE, but it doesn't seem to be welcome in a SELECT statement.

Ideas?

Thanks.

Thanks guys. I chose the answer that doesn't use COALESCE because it's the one I understood. I tried reading MS's explanation here: http://msdn.microsoft.com/en-us/library/ms190349.aspx and I still didn't fully understand, plus the note saying that "At least one of the null values must be a typed NULL" scared me off a bit. Thanks to all.

Upvotes: 1

Views: 1778

Answers (3)

mwigdahl
mwigdahl

Reputation: 16578

SELECT name, lastname, p1, p2, p3, p4, CASE WHEN COALESCE(p1, p2, p3, p4) IS NULL THEN 1 ELSE 0 END AS nulltest, city, state, zip
FROM mytable

Upvotes: 3

jcho360
jcho360

Reputation: 3759

try this:

    SELECT name, lastname, 
CASE WHEN  (p1 is null and p2 is null and p3 is null and p4 is null) then 1 
else 0 end , city, st, zip
    FROM mytable

Upvotes: 1

Sparky
Sparky

Reputation: 15085

Try 

select case
   coalesce(null,null,null,null,'X') 
   when 'X' then 1
   else 0
   end

Upvotes: 1

Related Questions