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
LVL 23
Steve WalesSenior Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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

0
PortletPaulfreelancerCommented:
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
0
chaauCommented:
@PortletPaul: In Oracle this does not work
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulfreelancerCommented:
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)
0
PortletPaulfreelancerCommented:
Oh, damn. ignore me then - too many dbms cross-overs
thanks chaau
0
chaauCommented:
@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

0
PortletPaulfreelancerCommented:
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.
0
Steve WalesSenior Database AdministratorAuthor Commented:
OK thanks to all for input, but while several of you were close, no one quite got there.

Here's what we had to end up doing.

First, taking of a small outage was necessary to lock the users out.

When doing an update of this type, the lookup table - the non updating table, needs to have a unique constraint on the join with the table that is being updated.

So, we created a copy of the onhand table - we'll call it temp1

create table temp1 as select stock_code, sum(soh) as soh from onhand group by stock_code
/

CREATE UNIQUE index temp1ix1 on temp1 (stock_code)
/

Open in new window


Then we

update
(
  select a.invent_cost_pr, a.invent_value / b.soh as new_invent_cost_pr
  from catalog a, temp1 b
  where a.stock_code = b.stock_code
) set invent_cost_pr = new_invent_cost_pr
/

Open in new window


Between PortletPaul and chaau we were close but the extra steps I had to go to asktom to get:   http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:273215737113

Without a unique key on the lookup table you get the following error message: ORA-01779: cannot modify a column which maps to a non key-preserved table
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Thanks for the detailed feedback.
0
Steve WalesSenior Database AdministratorAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.