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 |
|
vmon
Yak Posting Veteran
63 Posts |
 Posted - 2006-02-21 : 09:50:26
|
| I need to set a column to a sum but the query is not getting any records and I need it to be set to zero when there are no records. Here is what I have but don't know how to check for no recrods or sturcture statement to use zero when no records are selected.Thanks in advance,vmonUPDATE ASET A.ScreenQty = FaceFROM dbo.tblScheduleSummary A INNER JOIN (SELECT SUM(dbo.tblScheduleBom.QTY_PER_TOTAL) AS Face, dbo.tblScheduleBom.ORDER_NUMBER AS OrderNo FROM dbo.tblScheduleBom WHERE (dbo.tblScheduleBom.ScreenYN = 1) GROUP BY dbo.tblScheduleBom.ORDER_NUMBER)B ON A.OrderNumber = B.OrderNo |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-21 : 09:57:29
|
| If A.ScreenQty is assigned Null value and you want to set it to 0, try IsNull(Face,0) in place of FaceMadhivananFailing to plan is Planning to fail |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-21 : 10:01:05
|
| OR: Change the INNER JOIN in the subquery to a LEFT OUTER JOIN and then update a.sceenQty to IsNull(b.face, 0)Just be careful to include whatever WHERE criteria in the main update statement to limit updating only the rows you want to update (because the inner join won't do that any more)Be One with the OptimizerTG |
|
|
|
|
|
|
Rookie question I am sure