Need help handling NULL - 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 2005 Forums

Transact-SQL (2005)

Need help handling NULL

Author

Topic

tjwent69
Starting Member

30 Posts

Posted - 2007-09-10 : 15:43:04

I have an item table with a quantity on order field that is not correct with the actual quantites on open purchase orders.

Orderamt ItemNumber Description ItemOnOrder var
10 823130 SVEDKA CITRON 0 10
105 823020 SVEDKA 75 30
9 823060 SVEDKA 5 4
6 823140 SVEDKA CITRON 4 2
140 823030 SVEDKA 120 20
1 823440 SVEDKA VANILLA 0 1

I want to set the Bottle Quantity Order equal to what it should be.

update Item
Set Item.BottleQuantityOrdered =

(SELECT isnull(sum(OrderItem.ActualQuantitySold),0)
FROM Order Inner Join
OrderItem ON OrderItem.OrderId = Order.OrderID
WHERE Item.ItemNumber = OrderItem.CurrentItemNumber
and (Order.OrderTypeID = 'PO')
and (Order.IsCancelled = '0')
and Item.ItemSupplierID = '82'
Group by OrderItem.ItemNumber
Having (Item.BottleQuantityOrdered) - sum(OrderItem.ActualQuantitySold) <> 0
)

So the item will look like this.

Orderamt ItemNumber Description ItemOnOrder var
10 823130 SVEDKA CITRON 10 0
105 823020 SVEDKA 105 0
9 823060 SVEDKA 9 0
6 823140 SVEDKA CITRON 6 0
140 823030 SVEDKA 140 0
1 823440 SVEDKA VANILLA 1 0

When I execute my SQL I get the following error...

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'BottleQuantityOrdered', table 'UDPCO_PROD.dbo.Item'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

I don't know how this is NULL. Each of the items has at least one case on order. I even tried to use the isnull to set the column from the subquery to zero. I want to see what is NULL. When I run the select alone I get the Order Amounts. There is no NULL in the result set. What else could it be?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 15:46:47

[code]update Item
Set Item.BottleQuantityOrdered = isnull((
SELECT sum(OrderItem.ActualQuantitySold)
FROM Order
Inner Join OrderItem ON OrderItem.OrderId = Order.OrderID
WHERE Item.ItemNumber = OrderItem.CurrentItemNumber
and Order.OrderTypeID = 'PO'
and Order.IsCancelled = '0'
and Item.ItemSupplierID = '82'
Group by OrderItem.ItemNumber
Having Item.BottleQuantityOrdered <> sum(OrderItem.ActualQuantitySold)
), 0)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 15:50:12

[code]UPDATE x
SET x.BottleQuantityOrdered = ISNULL(y.AQS, 0)
FROM Item AS x
LEFT JOIN (
SELECT OrderItem.ItemNumber,
SUM(OrderItem.ActualQuantitySold) AS AQS
FROM Order
INNER JOIN OrderItem ON OrderItem.OrderId = Order.OrderID
WHERE Order.OrderTypeID = 'PO'
AND Order.IsCancelled = '0'
GROUP BY OrderItem.ItemNumber
) AS y ON y.CurrentItemNumber = x.ItemNumber
WHERE x.ItemSupplierID = '82'
AND x.BottleQuantityOrdered <> ISNULL(y.AQS, 0)[/code]

E 12°55'05.25"
N 56°04'39.16"

tjwent69
Starting Member

30 Posts

Posted - 2007-09-10 : 17:30:18

Brilliant! I understand the placement of the ISNull it needs to be on the actual value I am assigning not on the column in the sub-query. The second query was more of what I expected to happen. The first one affected all 5000 items the second changed only the 6. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources