Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2007
    Posts
    28

    Unanswered: help on calculation including nulls

    Hi everybody

    I have this sql which calculates the total

    MY TABLE IS:

    A B C

    100 5 3
    100 0 5
    20 0 0

    sql is

    A - B + C as total

    the result is it gives me only the total of the first row since it has values on the on the B AND C

    the result is

    A B C total

    100 5 3 98
    100 0 5
    20 0 0

    with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...

    thanks for any help

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Read up on the COALESCE function in the (online) help files.
    Code:
    COALESCE(Field, ValueIfNull)
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Posts
    28
    Thanks solved my problem and thanks for reminding me on the way of saying thanks in advance..

  4. #4
    Join Date
    May 2007
    Posts
    49
    you can also use isnull-

    select a, b, c, (isnull(A,0) - isnull(B, 0) + isnull(C,0)) as total
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    coalesce is generally preferred as it's ANSI. also it can take multiple args.

  6. #6
    Join Date
    May 2007
    Posts
    49
    Yes thats right, COALESCE have several advantages over ISNULL

    http://databases.aspfaq.com/database...snull-sql.html
    http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx

    readability is the only advantage of using ISNULL.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by mihirclarion
    readability is the only advantage of using ISNULL.
    readability??? what's more readable:

    select ISNULL(a, ISNULL(b, ISNULL(c,d)))

    select COALESCE(a,b,c,d)

    obviously I prefer coalesce.
    Last edited by jezemine; 06-16-07 at 11:29.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    IsNull is more readable imo.
    Before I learnt of coalesce I had no idea what it meant.
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    IsNull is more readable imo.
    only for two values, and even then it's six of one, six of the other

    ISNULL(foo,0)
    COALESCE(foo,0)

    now try it with multiple expressions, and see if ISNULL is really more readable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2007
    Posts
    49
    Readability -

    Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.

    this is what databases.aspfaq.com says.

    I just wanted to tell alexyeth about the alternative available.
    I alway prefer COALESCE.
    Last edited by mihirclarion; 06-18-07 at 00:59.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

    IMO - the fact you don't know what coalesce means is an advantage. Come across it and you have to look it up. Come across ISNULL as a vb programmer and you might think you know what it does.

    BOOOOOOOOO to T-SQL ISNULL!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by pootle flump
    IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.
    I'm pretty sure sql server inherited this function from sybase. doesn't change the fact that ISNULL is a stupid name for the function though.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Goddam Bill lover
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    eh, I have a soft spot for sql server, that's all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •