Post reply

Problem with NULL value

  • January 20, 2009 at 9:15 pm

    #199084

    Hi Friends,

    I have a small query.

    I have two variables which stores float values.

    I need to subtract 2nd variable from the first variable.

    Sometimes, the first or the second variable may be NULL.

    So, if I subtract the variable with the NULL value, NULL value is returned as ouput.

    I don't want this to happen.

    For example, NULL - 2 should return -2.

    1 - NULL should return 1.

    How to do this.

    Plz help me this is urgent.

    Thanks all.

  • January 20, 2009 at 9:21 pm

    #930063

    You can use the ISNULL function to return a 0 if the value is null.

    Select ISNULL(val1,0) - ISNULL(val2,0)

  • January 20, 2009 at 9:21 pm

    #930064

    the ISNULL function substitutes a value in place of the variable or column if it is null:

    SELECT ISNULL(@Variable1,0.00) - ISNULL(@Variable2,0.00)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 20, 2009 at 9:29 pm

    #930069

    Thank you friends for your fast reply.

    It worked. Thanks. 🙂

  • January 21, 2009 at 6:58 am

    #930294

    Another alternative to the ISNULL function is the COALESCE function; both will work. In addition, COALESCE will also work with both DB2 and Oracle.

  • January 21, 2009 at 7:48 pm

    #930849

    Thank you friend.

  • January 21, 2009 at 9:23 pm

    #930872

    Kent Waldrop (1/21/2009)

    Another alternative to the ISNULL function is the COALESCE function; both will work. In addition, COALESCE will also work with both DB2 and Oracle.

    In SQL Server, COALESCE is slower than ISNULL... granted, it takes a lot of operations to see the difference, but in certain instances (like concatenation) the speed differences show up quickly.

    Of course, there's the myth of code portability that a lot of folks still believe in. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply