Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Inline CASE WHEN ISNULL and multiple checks

I have a column with some nulls. If that column is null, I want to condition output for it based on values in another column.

So if case when null (if c=80 then 'planb'; else if c=90 then 'planc')

How would you code that in an inline T-SQL statement?

thanks.

like image 830
Hell.Bent Avatar asked May 09 '11 22:05

Hell.Bent


People also ask

Can we put multiple condition in case statement?

You can evaluate multiple conditions in the CASE statement.

Which is better coalesce or Isnull?

advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific.

What is the difference between coalesce () & Isnull ()?

Comparing COALESCE and ISNULL Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.


2 Answers

COALESCE(YourColumn, CASE c WHEN 80 then 'planb' WHEN 90 THEN 'planc' END)
like image 187
Martin Smith Avatar answered Oct 19 '22 18:10

Martin Smith


You can also use the nested case statement. Assuming that the first column is called DataColumn.

CASE 
  WHEN DataColumn IS NULL THEN 
    CASE c 
      WHEN 80 THEN 'planb' 
      WHEN 90 THEN 'planc' 
      ELSE 'no plan' 
    END 
  ELSE DataColumn 
END
like image 20
Leons Avatar answered Oct 19 '22 17:10

Leons