Case Statement

Hi!

I need a case that returns the result of a select if it is not null, and -1 if it is null. I did it this way:

select

case

when(select column from table where conditions) is null then -1

else(select column from table where conditions)

But it doesn't seem very clever to repeat the select statement. Is there any way I can do it without repeating the "select column from table where conditions"?

Thank you!

Try this:

select column = case column
when null then -1
else column
end
from table
where conditions

|||

You can write it like below which is ANSI SQL syntax:

select coalesce(column , -1) as column from table

COALESCE is just a short-hand for a special form of CASE expression like:

case when expr1 is not null then expr1

when expr2 is not null then expr2

...

end

Another proprietary TSQL method is to use isnull function:

select isnull(column, -1) as column from table

|||

Try either of

select coalesce(col, -1) from tab
go

select isnull(col, -1) from tab
go

Unfortunately, Allen's suggestion doesn't work because the "null" appearing in the when_expression causes it to always evaluate to false.

|||

Now it seems clever! :)

Thank you!!!

|||

Allen was almost there.

If you change this example slightly:

select column = case column
when null then -1
else column
end
from table
where conditions

to this..

select column = case
when column is null then -1
else column
end
from table
where conditions

..it'll work as expected

=;o)
/Kenneth