SQL Server Performance

CASE WHEN NULL ?

Discussion in 'General Developer Questions' started by tmarko, Feb 1, 2006.

  1. tmarko New Member

    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
  2. FrankKalis Moderator

  3. Madhivanan Moderator

    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
  4. tmarko New Member

    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 ...
  5. Madhivanan Moderator

    write IF(ISERROR([IS_WritedownGW]),0,[IS_WritedownGW])

    as

    COALESCE(ISERROR([IS_WritedownGW]),0,[IS_WritedownGW])

    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

    Ignore the ISERROR in Madhivanan answer.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Wrap every value that can be NULL with ISNULL or COALESCE.<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  7. Adriaan New Member

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

    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
  9. Adriaan New Member

    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.
  10. Madhivanan Moderator

    &gt;&gt;Ignore the ISERROR in Madhivanan answer.<br /><br />can be used if it is UDF [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  11. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />&gt;&gt;Ignore the ISERROR in Madhivanan answer.<br /><br />can be used if it is UDF [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">What type of UDF are you thinking of?<br /><br />[edit:]Hm, you must be thinking in SQL 2005, using CLR - right?
  12. Madhivanan Moderator

    Adriaan, actually I meant

    COALESCE((select dbo.ISERROR([IS_WritedownGW])),0,[IS_WritedownGW])


    Madhivanan

    Failing to plan is Planning to fail
  13. tmarko New Member

    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

  14. Adriaan New Member

    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.
  15. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by tmarko</i><br /><br />OK I think I solved it <br /><br />CASE WHEN [IS_Adjust] IS NULL <br />THEN [IS_NetIncome]-<br /> (CASE WHEN [IS_WritedownGW])IS NULL THEN 0 ELSE [IS_WritedownGW]) END) -<br />(CASE WHEN [IS_Writedown] IS NULL THEN 0 ELSE [IS_Writedown] END) -<br /> (CASE WHEN [IS_Restructuring] IS NULL THEN 0 ELSE [IS_Restructuring] END) -<br /> (CASE WHEN [IS_ExOrdItems] IS NULL THEN 0 ELSE [IS_ExOrdItems] END) -<br />(CASE WHEN [IS_TaxEO] IS NULL THEN 0 ELSE [IS_TaxEO END)<br /><br />ELSE ([IS_NetIncome]-[IS_Adjust])<br /> END<br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />No need to use CASE here - this is how short it can be:<br /><pre id="code"><font face="courier" size="2" id="code">[IS_NetIncome]-<br />ISNULL([IS_Adjust],<br />(ISNULL([IS_WritedownGW], 0) -<br /> ISNULL([IS_Writedown], 0) -<br /> ISNULL([IS_Restructuring], 0) -<br /> ISNULL([IS_ExOrdItems], 0) -<br /> ISNULL([IS_TaxEO], 0))<br />)</font id="code"></pre id="code">Short explanation on that:<br /><br />ISNULL(&lt;value&gt;, &lt;default&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />If &lt;value&gt; is null, then the &lt;default&gt; is returned, else &lt;value&gt; is returned.
  16. tmarko New Member

    Ok thanks thats good to know adriaan
  17. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">So this:<br />COALESCE((select dbo.ISERROR([IS_WritedownGW])),0,[IS_WritedownGW])<br /><br />... can be reduced to:<br />COALESCE(dbo.ISERROR([IS_WritedownGW]),0,[IS_WritedownGW])<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thats Good point [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page