3 Replies Latest reply: Apr 21, 2013 11:18 AM by NuhaArif RSS

    summary of alternate rows

    NuhaArif
      Hi , i have one table ow_ship_det, which contains Four fields sl_desc,sl_qty,sl_wt and sl_pm_code ,based on summary of sl_qty and sl_wt by sl_desc , i need to generate the serial numbers for group of sl_pm_code together , i want to add the qunatites and weight based on alternate rows per each sl_desc so that quantity and weight are evenly distributed and then i am inserting this result into another table called os_batch.test case as below.
      CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);
      
                                                                                               row
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); --1
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300); --2 
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300); --3
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('D','H170',10000,300); --4
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('E','I100',25000,700); --1
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('F','I100',25000,200); --2
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('G','J100',15000,200); --1
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('H','J100',15000,200); --2
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('I','J100',11000,200); --3
      insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('J','J100',11000,200); --4
      
      commit;
      
      --i will check the criteria based on this query whether sl_wt > 50000 and sl_qty > 1000, if they are more they will be under
      --one serial number but with balanced weight and balanced qty eually or proportionally distributed by picking the alternate rows.
      
      
      select a.*, sum (sl_wt) over(partition by sl_desc) sum_sl_wt,
                                          sum (sl_qty) over
                                            (partition by sl_desc) sum_sl_qty
                                          from (
      SELECT SL_PM_CODE,SL_DESC,SL_QTY,SL_WT FROM OW_SHIP_DET) a ;
      
      
      SL_PM_CODE     SL_DESC     SL_QTY     SL_WT     SUM_SL_WT     SUM_SL_QTY
      A                H170       300     15000     50000                1200 ---sl_qty is more than 1000 so we need 2 splits
      B                H170       300     15000     50000                1200
      C                H170       300     10000     50000                1200
      D                H170       300     10000     50000                1200
      E                I100       700     25000     50000                 900  --sl_qty and sl_wt are withing range hence no splits
      F                I100       200     25000     50000                 900
      G                J100       200     15000     52000                 800  --weight is more than 50,000 so we need two splits as follows
      H                J100       200     15000     52000                 800
      I                J100       200     11000     52000                 800
      J                J100       200     11000     52000                 800
      
      --i want the result in the os_batch table as follows
      
      CREATE TABLE OS_BATCH
      (
        OB_BATCH    VARCHAR2(12 BYTE),
        OB_PM_CODE  VARCHAR2(12 BYTE),
        OB_DESC     VARCHAR2(30 BYTE),
        OB_WT       NUMBER,
        OB_QTY      NUMBER
        );
      
      
      SL_PM_CODE     SL_DESC     SL_QTY     SL_WT     SUM_SL_WT     SUM_SL_QTY
      A                H170       300     15000     50000                1200 ---sl_qty is more than 1000 so we need 2 splits
      B                H170       300     15000     50000                1200
      C                H170       300     10000     50000                1200
      D                H170       300     10000     50000                1200
      E                I100       700     25000     50000                 900  --sl_qty and sl_wt are withing range hence no splits
      F                I100       200     25000     50000                 900
      G                J100       200     15000     52000                 800  --weight is more than 50,000 so we need two splits as follows
      H                J100       200     15000     52000                 800
      I                J100       200     11000     52000                 800
      J                J100       200     11000     52000                 800
      
      
      
      --Desired output in os_batch table
      
      
      ob_batch   OB_PM_CODE     OB_DESC     OB_QTY     OB_WT     
      0001          A        H170       300     15000     
      0001            C          H170       300     10000     
      0002          B        H170       300     15000     
      0002          D        H170       300     10000
      0003          E        I100       700     25000     
      0003          F        I100       200     25000     
      0004          G        J100       200     15000
      0004          I          J100       200     11000     
      0005          H          J100       200     15000     
      0005             J          J100       200     11000     
        • 1. Re: summary of alternate rows
          sb92075
          rows in a table are like balls in a basket.
          which ball is the next ball?
          • 2. Re: summary of alternate rows
            NuhaArif
            sorry sb for posting like that i will rewrite the test case again.

            see my next post.

            Edited by: 998476 on Apr 21, 2013 9:14 AM
            • 3. Re: summary of alternate rows
              NuhaArif
              Sorry for posting like that
              CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);
              
              
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('D','H170',10000,300); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('E','I100',25000,700); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('F','I100',25000,200); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('G','J100',15000,200); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('H','J100',15000,200); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('I','J100',11000,200); 
              insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('J','J100',11000,200); 
              
              commit;
              
              
              CREATE TABLE OS_BATCH
              (
              OB_BATCH VARCHAR2(12 BYTE),
              OB_PM_CODE VARCHAR2(12 BYTE),
              OB_DESC VARCHAR2(30 BYTE),
              OB_WT NUMBER,
              OB_QTY NUMBER
              );
              
              
              declare
                    v_batch    number;
                    v_wt       number := 0;
                    v_qty      number :=0;
                    v_desc    varchar2 (30 byte);
                   
                  begin
                    select nvl (max (to_number (nvl (ob_batch, '0'))), 0)+1
                    into   v_batch
                    from   os_batch;
                    for c1 in
                       (select sl_pm_code,sl_desc,sl_qty,sl_wt
                       from ow_ship_det order by sl_pm_code)
                         
                   loop
                      v_wt := v_wt + c1.sl_wt;
                      v_qty := v_qty + c1.sl_qty;
                      if v_wt > 50000 or v_qty > 1000 or
                          c1.sl_desc != v_desc then           
                         v_batch := v_batch + 1;
                         v_wt := c1.sl_wt;
                         v_qty := c1.sl_qty;
                         v_desc := c1.sl_desc;           
                      end if;
                      insert into os_batch (ob_batch, ob_pm_code, ob_desc, ob_wt, ob_qty)
                      values (lpad (v_batch, 4, '0'), c1.sl_pm_code, c1.sl_desc, c1.sl_wt, c1.sl_qty);
                   end loop;
                   commit;
                 end;
              
              
              --the output i am getting now is
              
              OB_BATCH     OB_PM_CODE     OB_DESC     OB_WT     OB_QTY     OB_TYPE
              0001          A                      H170     15000     300     null
              0001          B                      H170     15000     300     null
              0001          C                      H170     10000     300     null
              0002          D                          H170     10000     300     null
              0003          E                       I100     25000     700     null
              0003          F                       I100     25000     200     null
              0004          G                       J100     15000     200     null
              0004          H                       J100     15000     200     null
              0004          I                       J100     11000     200     null
              0005          J                       J100     11000     200     null
              
              
              
              --if you see above the weight is not balanced properly in batches 0001 for H170 Desc it should get divided equally as below
              
              ob_batch OB_PM_CODE OB_DESC OB_QTY OB_WT 
              0001                A            H170      300       15000 
              0001                C            H170      300       10000 
              0002                B            H170      300       15000 
              0002                D           H170       300       10000
              0003                E           I100        700       25000 
              0003                F           I100        200       25000 
              0004                G          J100        200       15000
              0004                I           J100       200        11000 
              0005               H           J100       200        15000 
              0005               J           J100       200         11000 
              Edited by: 998476 on Apr 21, 2013 9:18 AM