asked on

Update statement joining to another table returning nulls

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
  from
  (   -- 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
    join
    (
     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
    ) b
    on a.stock_code = b.stock_code
  ) e
  where s.stock_code = e.stock_code
)

Open in new window

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 ?

Thanks

chaau

I think you need to move the condition to filter against s.stock_code inside subquery e, like 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
  from
  (   -- 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
    join
    (
     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
    ) b
    on a.stock_code = b.stock_code
    where s.stock_code = b.stock_code
  ) e
)

Open in new window

PortletPaul

would this work for you?

SELECT
          s.invent_cost_pr
        , e.new_invent_cost_pr
FROM CATALOG s
INNER JOIN (
               SELECT
                      a.stock_code
                    , a.invent_value / decode(b.soh, 0, 1, b.soh) AS new_invent_cost_pr
               FROM CATALOG a
               INNER JOIN (
                            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
                            ) b ON a.stock_code = b.stock_code
           ) e ON s.stock_code = e.stock_code
;

UPDATE CATALOG s
INNER JOIN (
               SELECT
                      a.stock_code
                    , a.invent_value / decode(b.soh, 0, 1, b.soh) AS new_invent_cost_pr
               FROM CATALOG a
               INNER JOIN (
                            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
                            ) b ON a.stock_code = b.stock_code
           ) e ON s.stock_code = e.stock_code
SET s.invent_cost_pr = e.new_invent_cost_pr
;

Open in new window

{+ edit} alias corrections, sorry

chaau

@PortletPaul: In Oracle this does not work

PortletPaul

darn it, you need left joins, try with these changes please

lines 5, 21: LEFT JOIN
Line 3: , NVL(e.new_invent_cost_pr,0)
line 34: SET s.invent_cost_pr = NVL(e.new_invent_cost_pr,0)

PortletPaul

Oh, damn. ignore me then - too many dbms cross-overs
thanks chaau

chaau

@PortletPaul: BTW, there is an option in Oracle to convert your Select statement to UPDATE, like this:

UPDATE
(SELECT
          s.invent_cost_pr
        , e.new_invent_cost_pr
FROM CATALOG s
INNER JOIN (
               SELECT
                      a.stock_code
                    , a.invent_value / decode(b.soh, 0, 1, b.soh) AS new_invent_cost_pr
               FROM CATALOG a
               INNER JOIN (
                            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
                            ) b ON a.stock_code = b.stock_code
           ) e ON s.stock_code = e.stock_code
) t SET t.invent_cost_pr = t.new_invent_cost_pr;

Open in new window

PortletPaul

v.nice chaau, and coupling this with the left join then...

UPDATE (
        SELECT
                  s.invent_cost_pr
                , NVL(e.new_invent_cost_pr,0) AS new_invent_cost_pr
        FROM CATALOG s
        LEFT JOIN (
                       SELECT
                              a.stock_code
                            , a.invent_value / decode(b.soh, 0, 1, b.soh) AS new_invent_cost_pr
                       FROM CATALOG a
                       INNER JOIN (
                                    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
                                    ) b ON a.stock_code = b.stock_code
                   ) e ON s.stock_code = e.stock_code
        ) t
SET t.invent_cost_pr = t.new_invent_cost_pr
;

Open in new window

btw: an MySQL update I had just worked on is to blame.

ASKER CERTIFIED SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Thanks for the detailed feedback.

Steve Wales

ASKER

There were some decent pointers in the answer to the question, but I had to keep researching to find the answer. Eventually found the answer to this question myself.