Running Oracle 11gR2 on Oracle Enterprise Linux.
A colleague of mine is trying to run an update statement to update a column in one table based upon a calculated value from another table. We've put our heads together to try to resolve this issue but we're hitting a wall.
The table being updated is a table that contains stock items in the organization's inventory.
The table contains a column for the total inventory value of all items and the price per item.
So we could have 10 items at $12 each, the invent_value would be 120 and the invent_cost_pr would be 12.
There's a second table that contains the stock on hand for each stock code in each bin location in a warehouse.
So your 12 on hand could be split over 2 locations, 10 in bin1 and 2 in bin2.
A stock code may not have any rows on the on hand table (the item could have been entered into the catalog but hasn't been purchased yet) - there's a one to 0 or more relationship between catalog and onhand.
The customer has done some shuffling of their stock, moving some of the stock they have on hand from consignment (owned by someone else) to regular stock on hand.
They jumped into the database and changed all the consign_soh values to soh values and the invent_cost_pr is now off.
They want to write an update statement to revalue the invent_cost_pr based upon the new value of stock on hand.
There are 10221 rows in the catalog table and 8508 rows on the on hand table, of which 7488 are unique.
Oddly there are only 7402 rows when we join the unique rows (so there's obviously some rows on the on hand table that don't exist on the catalog table and I'm wondering if that's a part of the problem).
The statement we are running is this:
update catalog s
set s.invent_cost_pr =
select nvl2(e.new_invent_cost_pr, e.new_invent_cost_pr, 0) -- nvl2 added recently to see if this was the problem
( -- the following select gives, for each stock code, the new calculated invent_cost_pr
select a.stock_code, a.invent_value / decode(b.soh, 0, 1, b.soh) as new_invent_cost_pr
from catalog a
select h.stock_code, sum(h.soh) as soh from onhand h -- sums of total soh for stock code across all bin locations
group by h.stock_code
on a.stock_code = b.stock_code
where s.stock_code = e.stock_code
If I run the query part (everything from line 4 to line 15) I get a lovely list of stock codes and their new invent_cost_pr values.
As soon as we add the update around it, we get an error stating that catalog.invent_cost_pr cannot be set to null.
When we ran this for a small group of stock codes in initial testing (adding in an "and stock code in (1,2,3,4,5,6,7)") it worked just fine.
Now that we're trying to run it across the whole table, we're getting the error.
I feel that I'm missing something here - any of you SQL experts able to tell me what we're doing wrong here ?