SQL Server Performance Forum – Threads Archive

CASE WHEN NULL ?

I have the following IF clause How can I trasform it easily into SQL with CASE WHEN IS NULL? The combinations are quite a few (24)? and I cannot get a code that do not write out all these combinations IF(ISERROR([IS_Adjust]),[IS_NetIncome]-
IF(ISERROR([IS_WritedownGW]),0,[IS_WritedownGW])-
IF(ISERROR([IS_Writedown]),0,[IS_Writedown])-
IF(ISERROR([IS_Restructuring]),0,[IS_Restructuring])-
IF(ISERROR([IS_ExOrdItems]),0,[IS_ExOrdItems])-
IF(ISERROR([IS_TaxEO]),0,[IS_TaxEO]), [IS_NetIncome]-[IS_Adjust]) Anyone know how to write a CASE WHEN IS NULL

I don’t understand you. Are you looking for the ISNULL() function? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Can you post some sample data and the result you want Look for IsNull, COALESCE in BOL, SQL Server help file Madhivanan Failing to plan is Planning to fail

I want to transform the whole Excel formula to a SQL formula, but it’s a bit to complex to solve for me.
All values needs to be checked for IS NULL then those that IS NOT NULL should be added up unless IS_Adjust IS NULL then =>[IS_NetIncome]-[IS_Adjust] IF(ISERROR([IS_Adjust]),[IS_NetIncome]-
IF(ISERROR([IS_WritedownGW]),0,[IS_WritedownGW])-
IF(ISERROR([IS_Writedown]),0,[IS_Writedown])-
IF(ISERROR([IS_Restructuring]),0,[IS_Restructuring])-
IF(ISERROR([IS_ExOrdItems]),0,[IS_ExOrdItems])-
IF(ISERROR([IS_TaxEO]),0,[IS_TaxEO]), [IS_NetIncome]-[IS_Adjust]) ISERROR is like IS NULL Its hard to give an example beacuse vereything can be NULL or just one or between …

write IF(ISERROR([IS_WritedownGW]),0,[IS_WritedownGW]) as COALESCE(ISERROR([IS_WritedownGW]),0,[IS_WritedownGW]) Madhivanan Failing to plan is Planning to fail

Ignore the ISERROR in Madhivanan answer.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Wrap every value that can be NULL with ISNULL or COALESCE. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

If you have specific values that you can test for – no NULL – then use CASE <column/variable> WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE result_else END If you need to handle NULL separately from other values, then use CASE WHEN <column/variable> IS NULL THEN result_null ELSE CASE <column/variable> WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE result_else END END If you can handle NULL the same way as 0 or some default, then use CASE ISNULL(<column/variable>, <defaultvalue&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> WHEN value1 THEN result1 ………… END

Ok Adrian I need to separate the Null vales and only add upp values thar are NOT NULL. But to test all the combinations I get lost in the CASE WHEN tests. When you only have to test different 3 values it’s alright but 5 makes a lot of combinations. Maybe it’s enough to check the individual values once but then I need to store that tehy are OK or do I miss something regarding SQL

So you start with IS_Adjust, but when IS_Adjust is null then you start with IS_NetIncome – this you can write out as: ISNULL(IS_Adjust, IS_NetIncome) Then you do the same for other values that you’re deducting, replacing NULL with 0: ISNULL(IS_Adjust, IS_NetIncome)
– ISNULL(IS_WritedownGW, 0) etc. etc. This is actually easier in T-SQL than in your Excel (VBA) expressions.

>>Ignore the ISERROR in Madhivanan answer. can be used if it is UDF [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan >>Ignore the ISERROR in Madhivanan answer. can be used if it is UDF [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote">What type of UDF are you thinking of? [edit:]Hm, you must be thinking in SQL 2005, using CLR – right?

Adriaan, actually I meant COALESCE((select dbo.ISERROR([IS_WritedownGW])),0,[IS_WritedownGW])
Madhivanan Failing to plan is Planning to fail

OK I think I solved it CASE WHEN [IS_Adjust] IS NULL
THEN [IS_NetIncome]-
(CASE WHEN [IS_WritedownGW])IS NULL THEN 0 ELSE [IS_WritedownGW]) END) –
(CASE WHEN [IS_Writedown] IS NULL THEN 0 ELSE [IS_Writedown] END) –
(CASE WHEN [IS_Restructuring] IS NULL THEN 0 ELSE [IS_Restructuring] END) –
(CASE WHEN [IS_ExOrdItems] IS NULL THEN 0 ELSE [IS_ExOrdItems] END) –
(CASE WHEN [IS_TaxEO] IS NULL THEN 0 ELSE [IS_TaxEO END) ELSE ([IS_NetIncome]-[IS_Adjust])
END

quote:Originally posted by Madhivanan Adriaan, actually I meant COALESCE((select dbo.ISERROR([IS_WritedownGW])),0,[IS_WritedownGW])
Madhivanan Failing to plan is Planning to fail

Oh silly me! But remember that you don’t have to put SELECT in front of a UDF to get the result! So this:
COALESCE((select dbo.ISERROR([IS_WritedownGW])),0,[IS_WritedownGW]) … can be reduced to:
COALESCE(dbo.ISERROR([IS_WritedownGW]),0,[IS_WritedownGW]) … although I would expect that this ISERROR function already replaces a null with a zero.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by tmarko OK I think I solved it CASE WHEN [IS_Adjust] IS NULL THEN [IS_NetIncome]- (CASE WHEN [IS_WritedownGW])IS NULL THEN 0 ELSE [IS_WritedownGW]) END) - (CASE WHEN [IS_Writedown] IS NULL THEN 0 ELSE [IS_Writedown] END) - (CASE WHEN [IS_Restructuring] IS NULL THEN 0 ELSE [IS_Restructuring] END) - (CASE WHEN [IS_ExOrdItems] IS NULL THEN 0 ELSE [IS_ExOrdItems] END) - (CASE WHEN [IS_TaxEO] IS NULL THEN 0 ELSE [IS_TaxEO END) ELSE ([IS_NetIncome]-[IS_Adjust]) END <hr height="1" noshade id="quote"></blockquote id="quote"> No need to use CASE here – this is how short it can be: <pre id="code">[IS_NetIncome]- ISNULL([IS_Adjust], (ISNULL([IS_WritedownGW], 0) - ISNULL([IS_Writedown], 0) - ISNULL([IS_Restructuring], 0) - ISNULL([IS_ExOrdItems], 0) - ISNULL([IS_TaxEO], 0)) )</pre id="code">Short explanation on that: ISNULL(<value>, <default&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> If <value> is null, then the <default> is returned, else <value> is returned.

Ok thanks thats good to know adriaan

<blockquote id="quote">quote:<hr height="1" noshade id="quote">So this: COALESCE((select dbo.ISERROR([IS_WritedownGW])),0,[IS_WritedownGW]) … can be reduced to: COALESCE(dbo.ISERROR([IS_WritedownGW]),0,[IS_WritedownGW]) <hr height="1" noshade id="quote"></blockquote id="quote"> Thats Good point [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>