CODEWITHSUNDEEP
sql-server
burnt1ce
burnt1ce

Reputation: 14877

SQL Server : How to treat nulls as zeros

Here is an example of what I'm trying to do:

Select
S1.Name as S1Name,
S1.Itemcontent as S1ItemContent,
...
S2.Name as S2Name,
S2.Name as S2ItemContent,
**(S1.OrderValue * 1000*1000 + S2.OrderValue*1000 +  S3.OrderValue) as OrderValue**
From (joining s1, s2 and s3 on a particular value)
Order by OrderValue

When S1.OrderValue, S2.OrderValue or S3.OrderValue is a null, then OrderValue becomes a null as well. I want SQL Server to treat nulls as a 0 in this case. How can i do this?

Upvotes: 11

Views: 14220

Answers (6)

brad.huffman
brad.huffman

Reputation: 1271

You could use a case statement.

SELECT title, price,
        Budget = CASE price
         WHEN price > 20.00 THEN 'Expensive'
          WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
          WHEN price < 10.00 THEN 'Inexpensive'
          ELSE 'Unknown'
        END,
FROM titles

Returns the following:

Title                  Price       Budget
---------------------- ----------- ---------------
Cooking with Computers 11.95       Moderate
Straight Talk About Co 19.99       Moderate
The Busy Executive's D 19.99       Moderate
You Can Combat Compute 2.99        Inexpensive
Silicon Valley Gastron 19.99       Moderate
The Gourmet Microwave  2.99        Inexpensive
But Is It User Friendl 22.95       Expensive
Secrets of Silicon Val 20.00       Moderate
Net Etiquette          (null)      Unknown

Example taken from here (http://www.craigsmullins.com/ssu_0899.htm)

Upvotes: 3

John K
John K

Reputation: 28869

Use T-SQL Coalesce to force a non-null answer. For example:

COALESCE(S1.OrderValue, 0)

...will take the OrderValue value unless it is NULL in which case 0 is taken.

Upvotes: 4

Remus Rusanu
Remus Rusanu

Reputation: 294177

COALESCE(Value, 0) or ISNULL(Value, 0)

Upvotes: 5

Andrew
Andrew

Reputation: 27294

Using the isNull function, such as 

isnull(s1.ordervalue,0)

If the value is null, it uses the alternative value you provided as the second value.

Upvotes: 13

Wil P
Wil P

Reputation: 3371

Use ISNULL(S1.ORDERVALUE, 0)

Upvotes: 2

Gratzy
Gratzy

Reputation: 9389

Coalesce returns first non null value. 

**(coalesce(S1.OrderValue,0) * 1000 + coalesce(S2.OrderValue,0)*1000 +  coalesce(S3.OrderValue,0)) as OrderValue

Upvotes: 3

Related Questions