Solved

Oracle - Query Insert and Update multiple tables

Posted on 2016-11-10
5
38 Views
Last Modified: 2016-11-14
I am querying some tables and want to insert/update the result to multiple tables.  I cannot keep querying to do each insert/update since the data will change from my original query.  So whatever the result is, I want that to be inserted into a few tables and use the same data to update some tables.

The below screenshot is result from my query.  The field "PACKLIST_ID" query from a table and have that increment by 1 based off the "ORDER_ID".  The field "TRANSACTION_ID" query the max value from the INVENTORY_TRANS table to get highest value and also increment by 1 if the order_line_no is not 0.  These 2 fields are important since they will be the primary key for some of the tables i am inserting into.
Result from query
Is it possible to do something similar below?  Can someone take a look and help tweak to do similar?  I basically take my result from my query and inserting/updating various tables until end of record.  I was thinking of query insert into each table at a time, but again my result will change each time it query the tables and I cannot have that.

DECLARE

CURSOR C1 IS

SELECT  X.PACKLIST_ID+DENSE_RANK() OVER (ORDER BY X.ORDER_ID) PACKLIST_ID, 
        X.NEXT_NUM+DENSE_RANK() OVER (ORDER BY X.ORDER_ID) NEXT_NUM, 
        X.ORDER_ID, X.ORDER_LINE_NO, X.SHIP_QTY, X.UNIT_PRICE, X.TRANSACTION_ID
FROM    (
        SELECT  (SELECT PREFIX||LPAD(NEXT_NUM-1, LENGTHS, '0')||SUFFIX FROM NEXT_NUMBER WHERE TABLE_NAME = 'SHIPPER')  PACKLIST_ID,
                (SELECT NEXT_NUM-1 FROM NEXT_NUMBER WHERE TABLE_NAME = 'SHIPPER')  NEXT_NUM,
                CO.ID ORDER_ID, 0 ORDER_LINE_NO, 0 SHIP_QTY, 0 UNIT_PRICE, 0 TRANSACTION_ID
        FROM    CUST_ORDER CO
        WHERE   CO.ID IN ('607018', '607019')
        UNION
        SELECT  (SELECT PREFIX||LPAD(NEXT_NUM-1, LENGTHS, '0')||SUFFIX FROM NEXT_NUMBER WHERE TABLE_NAME = 'SHIPPER')  PACKLIST_ID,
                (SELECT NEXT_NUM-1 FROM NEXT_NUMBER WHERE TABLE_NAME = 'SHIPPER')  NEXT_NUM,
                COLD.ORDER_ID, COLD.LINE_NO ORDER_LINE_NO, COLD.CUST_ORDER_QTY SHIP_QTY, COLD.UNIT_PRICE, 
                DENSE_RANK() OVER (ORDER BY COLD.ORDER_ID, COLD.LINE_NO) + (SELECT MAX(TRANSACTION_ID) FROM INV_TRANS) TRANSACTION_ID
        FROM    CUST_ORDER_LINE_DETAIL COLD   
        WHERE   COLD.ORDER_ID IN ('607018', '607019')
        ) X
ORDER BY X.ORDER_ID, X.ORDER_LINE_NO;

BEGIN
  FOR I IN C1
    LOOP
      BEGIN
      
        INSERT INTO SHIPPER(PACKLIST_ID, ORDER_ID, SHIPPED_DATE, CREATE_DATE)
        SELECT  I.PACKLIST_ID, I.ORDER_ID, SYSDATE, SYSDATE 
        FROM    C1 I 
        WHERE   I.ORDER_LINE_NO = 0;
        
        INSERT INTO SHIPPER_LINE(PACKLIST_ID, LINE_NO, ORDER_ID, ORDER_LINE_NO, SHIP_QTY, UNIT_PRICE, TRANSACTION_ID)
        SELECT  I.PACKLIST_ID, I.ORDER_LINE_NO, I.ORDER_ID, I.ORDER_LINE_NO, I.SHIP_QTY, I.UNIT_PRICE, I.TRANSACTION_ID
        FROM    C1 I
        WHERE   I.ORDER_LINE_NO <> 0;
        
        INSERT INTO INV_TRANS(TRANSACTION_ID, ORDER_ID, ORDER_LINE_NO, TRANSACTION_DATE, TYPE, CLASS)
        SELECT  I.TRANSACTION_ID, I.ORDER_ID, I.ORDER_LINE_NO, SYSDATE, 'O', 'I'
        FROM    C1 I
        WHERE   I.ORDER_LINE_NO <> 0;
        
        UPDATE  NEXT_NUMBER
        SET     NEXT_NUM = MAX(I.NEXT_NUM)
        WHERE   TABLE_NAME = 'SHIPPER';
        
        UPDATE CUST_ORDER_LINE_DETAIL
        SET   TOTAL_SHIPPED_QTY = I.SHIP_QTY
        WHERE ORDER_ID = I.ORDER_ID AND LINE_NO = I.ORDER_LINE_NO;
        
        UPDATE CUST_ORDER
        SET TOTAL_AMT_SHIPPED = SUM(I.SHIP_QTY * I.UNIT_PRICE)
        WHERE ID = I.ORDER_ID AND I.ORDER_LINE_NO <> 0;
        
      END;
    END LOOP;
END;

Open in new window

0
Comment
Question by:holemania
  • 3
  • 2
5 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
I don't see why you would need to select from the cursor/table again?

Can you not just insert the row you are on inside the loop?  

...
if I.ORDER_LINE_NO = 0 then
      INSERT INTO SHIPPER(PACKLIST_ID, ORDER_ID, SHIPPED_DATE, CREATE_DATE)
        values( I.PACKLIST_ID, I.ORDER_ID, SYSDATE, SYSDATE );
end if;
...

>>  UPDATE  NEXT_NUMBER  SET     NEXT_NUM = MAX(I.NEXT_NUM) WHERE   TABLE_NAME = 'SHIPPER';

This won't work. you would need to select the MAX from the table after you are all done.

There is also a problem doing that.  If two people run this about the same time the MAX will be different between them before they each commit.

For example:
user1 runs is and inserts 10 rows so the max is 10 but hasn't issued a commit yet.
user2 runs is and inserts 10 rows so the max is 10 but hasn't issued a commit yet.
user1 commits;
user2 commits;

What is the MAX value in the table?

>>SET TOTAL_AMT_SHIPPED = SUM(I.SHIP_QTY * I.UNIT_PRICE)

This won't work either.  There is no SUM that way.  If you want the product of the two columns just get it:
SET TOTAL_AMT_SHIPPED = I.SHIP_QTY * I.UNIT_PRICE
0
 

Author Comment

by:holemania
Comment Utility
Thanks for the help.  I can use the "if else then" to do the insert if I can control what goes into which table similar to my select statement with the "Where" clause.  I will take a look again.

As for the MAX question, only one person will be executing this tool.  Basically when the tool is run to insert/update the tables, all transactions are not to be happening in the database.  So this would not be an issue.  I understand where you're coming from with this, but we have a process in place.

I can create a second cursor to look at the table after I am done with all the insert to get the max value and update the NEXT_NUMBER table.

As for the total amount, I want to sum the total qty * unit price for each line and then sum that into the CUST_ORDER table.  So if sales order 607019 would have 2 lines which sum total of $320.
0
 

Author Comment

by:holemania
Comment Utility
Actually the MAX, I will just do a select statement for max number and update that table.  So no need for cursor.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Actually the MAX, I will just do a select statement for max number and update that table.  So no need for cursor.

Good catch!  I was just getting ready to comment on that.

>> I want to sum the total qty * unit price for each line and then sum that into the CUST_ORDER table

You can create a local variable and keep adding to it inside the loop or do an update select sum() outside the loop once everything is done.

If you use the local variable, you will need to keep track of when the order_id changes to the next one then update CUST_ORDER and reset your variable.
0
 

Author Closing Comment

by:holemania
Comment Utility
Thanks again.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now