CODEWITHSUNDEEP
phpsqldb2
Geoff_S
Geoff_S

Reputation: 5107

Inserting into two tables based on specific grouping in query

I'm working on a script where I'm trying to take results from a query:

    SELECT
        OrdNumber,
        Detail1,
        Detail2,
        Detail3, 
        Date,
        Quantity,
        Customer,
        OrderingCode

    FROM Orders o
    INNER JOIN (SELECT os.*,
         row_number() over (partition by Detail1, Detail2,Detail3 ) as seqnum
    FROM orderSheets os
    ) os
      on o.product = os.product and
      o.Detail2 = os.Detail2 and
      o.Detail3 = os.Detail3

And take the necessary data from there, based on certain conditions, and insert records associated into two tables.

I want to insert based on grouping each record by the distinct Detail1, Detail2, and Detail3 for that customer on that order, and then also look at the highest quantity if I have multiple records for the same detail1/detail2/detail3/customer unique grouping

With the query above, say I return these results:

    OrdNumber |  Detail1 |  Detail2 |  Detail3 |     Date    | Quantity | Customer  | OrderingCode
    -----------------------------------------------------------------------------------------
    12345         122         123         12     12/12/2018        2       123         567
    12345         122         123         15     12/12/2018        2       123         567
    12345         516         123         63     12/12/2018        5       123         567
    12345         617         123         14     12/12/2018        7       123         567
    12345         617         123         14     12/12/2018        4       123         567
    12345         617         123         17     12/12/2018        2       123         567
    12345         745         123         43     12/12/2018        2       123         567

Most of the results are already distinct but I have two records in there that share the same detail1, detail2 and detail3 for that customer on that order (617, 123, 14) so in that case I want to insert only one record into the products table for those two results (the distinct details, customer number, order number, date, and orderingCode) and one foreign related record to the orders table but i only want to insert the highest quantity of the 2 records (So in that example I would insert the order number, the foreign key which is the products ID, and 7 as the quantity since it's the highest of the two records having 7 and 4)

SO with the result set above, I would want to product inserts to the two tables like so:

Products 

    ID   | Detail1 | Detail2 | Detail3 | Customer | OrderingCode |    Date 
    ----------------------------------------------------------------------------   
    1001    122        123       12        123        567          12/12/2018  
    1002    122        123       15        123        567          12/12/2018  
    1003    516        123       63        123        567          12/12/2018  
    1004    617        123       14        123        567          12/12/2018  
    1005    617        123       17        123        567          12/12/2018  
    1006    745        123       43        123        567          12/12/2018

Orders 

    P_ID  |  Quantity  |      Date    |  orderNumber  
    -------------------------------------------------           
    1001         2        12/12/2018      12345               
    1002         2        12/12/2018      12345               
    1003         5        12/12/2018      12345               
    1004         7        12/12/2018      12345               
    1005         2        12/12/2018      12345               
    1006         2        12/12/2018      12345

Upvotes: 1

Views: 75

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

A little bit complicated, but try this AS IS:

create table Products (
  ID int not null generated always as identity primary key
, Detail1 int not null
, Detail2 int not null
, Detail3 int not null
, Customer int not null
, OrderingCode int not null
, Date date not null
) in userspace1;

create table Orders(
  P_ID int not null
, Quantity int not null
, Date date not null
, orderNumber int not null
, constraint orders_fk foreign key (p_id) references Products (id)
) in userspace1;

with r (OrdNumber, Detail1, Detail2, Detail3, Date, Quantity, Customer, OrderingCode) as (values
  (12345, 122, 123, 12, '12/12/2018', 2, 123, 567)
, (12345, 122, 123, 15, '12/12/2018', 2, 123, 567)
, (12345, 516, 123, 63, '12/12/2018', 5, 123, 567)
, (12345, 617, 123, 14, '12/12/2018', 7, 123, 567)
, (12345, 617, 123, 14, '12/12/2018', 4, 123, 567)
, (12345, 617, 123, 17, '12/12/2018', 2, 123, 567)
, (12345, 745, 123, 43, '12/12/2018', 2, 123, 567)
)
, p as (
select ID, Quantity, Date, ordNumber
from new table (
insert into products (Detail1, Detail2, Detail3, Customer, OrderingCode, Date)
include (OrdNumber int, Quantity int)
select Detail1, Detail2, Detail3, Customer, OrderingCode, Date, OrdNumber, Quantity
from (
select Detail1, Detail2, Detail3, Customer, OrderingCode, Date, OrdNumber, Quantity
, rownumber() over (partition by Detail1, Detail2, Detail3 order by Quantity desc) rn_
from r
) where rn_=1
)
)
select count(1) 
from new table (
insert into Orders (P_ID, Quantity, Date, orderNumber)
select ID, Quantity, Date, ordNumber
from p
);

In the 1-st WITH clause (referenced as r) there is a result set you get with your query. Couple selects from a data-change operation is used then.

1-st one (referenced as p) inserts needed rows into Products and gets generated IDs for these rows.

2-nd one (the latest) inserts rows into Orders using generated IDs and other fields needed.

Upvotes: 1

Related Questions