Problem with NULL value
-
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.
-
You can use the ISNULL function to return a 0 if the value is null.
Select ISNULL(val1,0) - ISNULL(val2,0)
-
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! -
Thank you friends for your fast reply.
It worked. Thanks.
-
Another alternative to the ISNULL function is the COALESCE function; both will work. In addition, COALESCE will also work with both DB2 and Oracle.
-
Thank you friend.
-
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)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply