SQL help with Nulls - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL help with Nulls

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-20 : 16:34:20

The query returns 2 null values. How can I return 0 instead?

SELECT SUM (((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100))) As Total, SUM(OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Cost) As Cost
FROM OEHDRHST_SQL, OELINHST_SQL
WHERE OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
AND OEHDRHST_SQL.Ord_dt >=20040801
AND OEHDRHST_SQL.Ord_dt <=20040831
AND OELINHST_SQL.prod_cat = '#qCats.prod_cat#'

jmangione
Starting Member

7 Posts

Posted - 2004-10-20 : 16:41:47

This ought to work...

SELECT isnull(SUM (((OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Price) * ((100 - OELINHST_SQL.Discount_Pct) / 100))),0) As Total, isnull(SUM(OELINHST_SQL.Qty_Ordered * OELINHST_SQL.Unit_Cost),0) As Cost
FROM OEHDRHST_SQL, OELINHST_SQL
WHERE OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
AND OEHDRHST_SQL.Ord_dt >=20040801
AND OEHDRHST_SQL.Ord_dt <=20040831
AND OELINHST_SQL.prod_cat = '#qCats.prod_cat#'

If, however, the fields themselves are null, you need to add isnull(Fldname, 0) to each part of the equation as if you try to perform a mathematical operation with a null, you get a null. Example 2*null = null so you have to isnull each field.

Hope this makes sense!

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-20 : 16:43:41

Perfect! Thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources