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.
| Author |
Topic |
|
btamulis
Yak Posting Veteran
64 Posts |
 Posted - 2008-07-19 : 09:50:16
|
| I have a SQL view which is returning 'null' when I desire it to return of 'zero' instead of the 'null'Here is my view script:ALTER VIEW [dbo].[TEC_PO_BALANCE_DUE]ASSELECT POP10110.PONUMBER, POP10110.ITEMNMBR, POP10110.ORD/16384 as LINEITEMNUMBER, POP10110.QTYORDER, POP10110.QTYCANCE, QTYS.QTYRECEIVED, QTYS.QTYINVOICED, QTYS.QTYMATCHED FROM POP10110 Left Outer Join (select PONUMBER, SUM(QTYSHPPD) , SUM(QTYINVCD) , SUM(QTYMATCH) , POLNENUMfrom POP10500 GROUP BY POP10500.PONUMBER, POLNENUM) QTYS on POP10110.PONUMBER = QTYS.PONUMBER and POP10110.ORD = QTYS.POLNENUMI know why it returns 'null' - In some cases, the PONUMBER in the POP10110 table is not present in the POP10500 table - thus the view retuns NULL for the columns QTYRECEIVED, QTYINVOICED, QTYMATCHED - in those cases I want zeros instead of nulls.......I tried several uses of the not null statement - all failed. Any suggestions would be appreciated......Thank you in advance..... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-19 : 09:52:21
|
use isnull(col, 0) or coalesce(col, 0)
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
btamulis
Yak Posting Veteran
64 Posts |
Posted - 2008-07-19 : 09:59:19
|
| Khtan,Thank you for your quick response. Where would I insert the is null - would I place it before the join or do I place it in the select statement that sums the non-existent values (after the join)? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-19 : 10:08:06
|
[code]SELECT POP10110.PONUMBER, POP10110.ITEMNMBR, POP10110.ORD/16384 as LINEITEMNUMBER, POP10110.QTYORDER, POP10110.QTYCANCE, ISNULL(QTYS.QTYRECEIVED, 0) AS QTYRECEIVED, ISNULL(QTYS.QTYINVOICED, 0) AS QTYINVOICED, ISNULL(QTYS.QTYMATCHED, 0) AS QTYMATCHED,[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
btamulis
Yak Posting Veteran
64 Posts |
Posted - 2008-07-19 : 10:17:36
|
| Worked like a champ......Thank you very much........ |
|
|
|
|
|
|
|
|
Not Null - how to use?