Change NULL value to 0 - 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

Transact-SQL (2000)

Change NULL value to 0

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-08-07 : 00:16:50

I had to adjust my query to make NULL become 0. Till now, still struggle.

This is my table
tITem
ItmLocation | ItmCode | ItmDsc | ItmPrc | ItmMax
--------------------------------------------------------------------
KU | 206622 | YES | 7.2 | 3
KU | 221694 | Nalgene | 4.2 | 4
KU | 226699 | JOKI | 8.1 | 3
KU | 231610 | BUL | 7.70 | 3
KU | 231621 | TAT | 5.10 | 3
....
....
....

tOrder
OrdNo | OrdEmpNo | OrdDte | OrdLocation | OrdItm | OrdQty | OrdAmt | OrdProcceesed | OrdDlv
---------------------------------------------------------------------------------------------------------------------
107848 | 16005 | 6/22/2008 | KU |206622 | 2 | 14.4 | 6/22/2008 | 2
107902 | 16005 | 6/22/2008 | KU |221694 | 3 | 12.6 | 6/22/2008 | 3
107311 | 16005 | 6/22/2008 | KU |226699 | 2 | 16.2 | 6/22/2008 | 2
....
....
....

************************************************************************************************
SELECT Itm, ItmDsc, ItmPrc, ItmMax,
OrdQty=SUM(OrdQty), OrdDlv=SUM(OrdDlv),
OrdQuotaLeft=CASE WHEN SUM(OrdQty) IS NULL THEN ItmMax ELSE ItmMax - SUM(OrdQty) END
FROM tmItm
LEFT OUTER JOIN Torder ON Itm = OrdItm
AND OrdEmpNo = '16005'
AND month(OrdDte) = month(GetDate())
AND year(OrdDte) = year(GetDate())
WHERE ItmWhs = 'KU'
GROUP BY Itm, ItmDsc, ItmPrc, ItmMax, OrdItm

Itm |ItmDsc |ItmPrc | ItmMax | OrdQty | OrdDlv |OrdQuotaLeft
------------------------------------------------------------------------------------
206622 YES 7.2 3 2 2 1
221694 Nalgene 4.2 4 3 3 1
226699 JOKI 8.1 3 2 2 1
231610 BUL 7.7 3 NULL NULL 3
231610 TAT 5.1 3 NULL NULL 3
...
...

How to adjust my above query to make it NULL become 0, so the result become

Itm |ItmDsc |ItmPrc | ItmMax | OrdQty | OrdDlv |OrdQuotaLeft
------------------------------------------------------------------------------------
206622 YES 7.2 3 2 2 1
221694 Nalgene 4.2 4 3 3 1
226699 JOKI 8.1 3 2 2 1
231610 BUL 7.7 3 0 0 3
231610 TAT 5.1 3 0 0 3
...
...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-07 : 02:44:53

look at coalesce() and isnull() in books online

Em

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-08-07 : 04:03:53

tq very much for the info :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources