Solved

Oracle - Query Insert and Update multiple tables

Posted on 2016-11-10
5
77 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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41882703
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
ID: 41882863
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
ID: 41882866
Actually the MAX, I will just do a select statement for max number and update that table.  So no need for cursor.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41882871
>>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
ID: 41887054
Thanks again.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

838 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