This discussion is archived
9 Replies Latest reply: Apr 26, 2013 8:38 AM by William Robertson RSS

Grouping reocords based on odd and even rows

NuhaArif Newbie
Currently Being Moderated
I have a sets of data in table ow_ship_det , from which i want to group all the records which are having same sl_desc but with the condition that sl_qty is not more than 1000 and sl_wt not more than 50000, i managed to do it but the problem is i want the wieght(sl_wt) and qty(sl_qty) to be evenly distributed among groups or boxes for example take the first four records which have common sl_desc 'H170' Where the qauntities are 15000,15000,10000,10000 as per the condition and loop written in program it will bring the 2 boxes or serial numbers with first 3 weights into 1 box as 40000 and other box as 10000, which i dont want instead i want to have them as 25000 each.

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
  • 1. Re: Grouping reocords based on odd and even rows
    sb92075 Guru
    Currently Being Moderated
    rows in a table have NO inherent order

    Re: summary of alternate rows
  • 2. Re: Grouping reocords based on odd and even rows
    NuhaArif Newbie
    Currently Being Moderated
    big boss the order of rows is by sl_desc and that we have to do it in query or cursor.
  • 3. Re: Grouping reocords based on odd and even rows
    Purvesh K Guru
    Currently Being Moderated
    998476 wrote:
    I have a sets of data in table ow_ship_det , from which i want to group all the records which are having same sl_desc but with the condition that sl_qty is not more than 1000 and sl_wt not more than 50000, i managed to do it but the problem is i want the wieght(sl_wt) and qty(sl_qty) to be evenly distributed among groups or boxes for example take the first four records which have common sl_desc 'H170' Where the qauntities are 15000,15000,10000,10000 as per the condition and loop written in program it will bring the 2 boxes or serial numbers with first 3 weights into 1 box as 40000 and other box as 10000, which i dont want instead i want to have them as 25000 each.
    Description explains it to be similar to Knapsack problem.

    Soloman has posted a brilliant solution that might assist you, but you will have to adapt to the solution. {message:id=10757833}
  • 4. Re: Grouping reocords based on odd and even rows
    NuhaArif Newbie
    Currently Being Moderated
    dear purvesh , i could not understand what solomon wrote and appreciate if someone could demonstrate it using my test case.
  • 5. Re: Grouping reocords based on odd and even rows
    jeneesh Guru
    Currently Being Moderated
    As per your sample data and expected output, the below work..

    If this is not what you want, please provide more samples that will cover all your scenarios..
    SQL> with ord_data as
      2  (
      3  select SL_PM_CODE,SL_DESC,SL_WT,SL_QTY,
      4         row_number() over(partition by sl_desc order by sl_wt,sl_pm_code )  rn1,
      5         row_number() over(partition by sl_desc order by sl_wt desc,sl_pm_code)  rn2
      6  from  OW_SHIP_DET
      7  ),
      8  rnk_data as
      9  (
     10  select SL_PM_CODE,SL_DESC,SL_WT,SL_QTY,
     11         row_number() over( order by sl_desc,least(rn2,rn1),sl_pm_code ) rnk
     12  from ord_data
     13  )
     14  select  to_char(dense_rank() over(order by floor((rnk-1)/2)),'0000') ob_batch,
     15     SL_PM_CODE,SL_DESC,SL_WT,SL_QTY
     16  from rnk_data;
    
    OB_BA SL_PM_CODE   SL_DESC                   SL_WT     SL_QTY
    ----- ------------ -------------------- ---------- ----------
     0001 A            H170                      15000        300
     0001 C            H170                      10000        300
     0002 B            H170                      15000        300
     0002 D            H170                      10000        300
     0003 E            I100                      25000        700
     0003 F            I100                      25000        200
     0004 G            J100                      15000        200
     0004 I            J100                      11000        200
     0005 H            J100                      15000        200
     0005 J            J100                      11000        200
    
    10 rows selected.
    Edited by: jeneesh on Apr 22, 2013 2:23 PM
    For example: This code will not handle the case in which one SL_DESC is having odd number of records.. You will have to explain what is the expected out put in that case..
  • 6. Re: Grouping reocords based on odd and even rows
    NuhaArif Newbie
    Currently Being Moderated
    dear jeenesh , first like to thank you for the solution, well actually this is just a sample data , to be more precise i have a large table with too many records of sl_pm_code,sl_desc,sl_qty,sl_wt and criteria for assigning serial number or batch is based on condition that if sl_qty>1000 and sl_wt>50000 exceeds it will have 2 or more splits or serial numbers,like for this case as given below. i have prepared another test case.if you see my first post i managed to put them under three batches but the problem is they have to be balanced properly , like if there are 3 splits , then the total qty of 67216 for sl_desc should be as 22405,22405,22405 not as 50,000 and 17000.Please do let me know if you need more clarification.
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1064', 'HEA160', 211.837, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1065', 'HEA160', 171.62, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1066', 'HEA160', 387.442, 20); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1067', 'HEA160', 387.442, 20); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1068', 'HEA160', 197.646, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1069', 'HEA160', 197.646, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1070', 'HEA160', 175.161, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1071', 'HEA160', 175.161, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1072', 'HEA160', 175.161, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1073', 'HEA160', 175.161, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1074', 'HEA160', 437.83, 28); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1075', 'HEA160', 437.83, 28); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1076', 'HEA160', 230.175, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1077', 'HEA160', 230.175, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1078', 'HEA160', 230.175, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1079', 'HEA160', 230.175, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1080', 'HEA160', 4024.903, 266); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1081', 'HEA160', 2118.33, 140); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1082', 'HEA160', 1544.544, 72); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1083', 'HEA160', 1372.928, 64); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1084', 'HEA160', 686.46, 32); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1085', 'HEA160', 514.851, 24); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1086', 'HEA160', 171.62, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1087', 'HEA160', 97.154, 7); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1088', 'HEA160', 97.154, 7); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1089', 'HEA160', 121.893, 7); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1090', 'HEA160', 121.893, 7); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1029', 'HEA200', 362.982, 38); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1030', 'HEA200', 362.982, 38); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1031', 'HEA200', 362.982, 38); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1032', 'HEA200', 362.982, 38); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1033', 'HEA200', 394.068, 44); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1034', 'HEA200', 326.585, 32); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1035', 'HEA200', 353.287, 34); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1036', 'HEA200', 353.287, 34); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1037', 'HEA200', 325.735, 35); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1038', 'HEA200', 325.735, 35); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1039', 'HEA200', 325.735, 35); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1040', 'HEA200', 325.735, 35); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1041', 'HEA200', 325.145, 35); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1042', 'HEA200', 319.058, 24); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1043', 'HEA200', 319.058, 24); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1044', 'HEA200', 1567.04, 160); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1045', 'HEA200', 1880.448, 192); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1046', 'HEA200', 313.408, 32); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1047', 'HEA200', 313.408, 32); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1048', 'HEA200', 359.095, 38); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1049', 'HEA200', 359.095, 38); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1050', 'HEA200', 359.095, 38); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1051', 'HEA200', 257.662, 23); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1052', 'HEA200', 303.349, 29); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1053', 'HEA200', 257.662, 23); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1054', 'HEA200', 257.662, 23); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1055', 'HEA200', 257.662, 23); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1056', 'HEA200', 303.349, 29); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1057', 'HEA200', 303.349, 29); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1062', 'HEA200', 163.848, 7); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1063', 'HEA200', 163.848, 7); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-1091', 'HEA200', 336.028, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2022', 'HEA200', 271.002, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2023', 'HEA200', 271.002, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2024', 'HEA200', 271.002, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2025', 'HEA200', 271.002, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2026', 'HEA200', 280.468, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2027', 'HEA200', 139.013, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2028', 'HEA200', 1640.658, 72); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2029', 'HEA200', 292.539, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2030', 'HEA200', 292.139, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2031', 'HEA200', 292.139, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2032', 'HEA200', 1640.658, 72); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2033', 'HEA200', 292.539, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2034', 'HEA200', 291.718, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2035', 'HEA200', 291.718, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2036', 'HEA200', 291.718, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2037', 'HEA200', 291.718, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2038', 'HEA200', 292.139, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2039', 'HEA200', 292.139, 14); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2040', 'HEA200', 1640.658, 72); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2041', 'HEA200', 1640.658, 72); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2042', 'HEA200', 235.764, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2043', 'HEA200', 471.538, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2044', 'HEA200', 471.538, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2045', 'HEA200', 235.764, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2046', 'HEA200', 151.251, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2047', 'HEA200', 151.251, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2048', 'HEA200', 151.251, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2049', 'HEA200', 151.251, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2050', 'HEA200', 235.764, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2051', 'HEA200', 258.853, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2052', 'HEA200', 258.853, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2053', 'HEA200', 256.347, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2054', 'HEA200', 256.347, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2055', 'HEA200', 256.347, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2056', 'HEA200', 256.347, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2057', 'HEA200', 235.764, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2058', 'HEA200', 1414.602, 30); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2059', 'HEA200', 1414.602, 30); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2060', 'HEA200', 235.764, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2061', 'HEA200', 235.764, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2062', 'HEA200', 1178.84, 25); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2063', 'HEA200', 1414.602, 30); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2064', 'HEA200', 235.764, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2065', 'HEA200', 193.508, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2066', 'HEA200', 193.508, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2067', 'HEA200', 193.508, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2068', 'HEA200', 387.026, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2069', 'HEA200', 387.026, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2070', 'HEA200', 193.508, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2071', 'HEA200', 193.508, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2072', 'HEA200', 387.026, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2073', 'HEA200', 80.884, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2074', 'HEA200', 80.884, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2075', 'HEA200', 353.16, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2076', 'HEA200', 312.702, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2077', 'HEA200', 277.654, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2078', 'HEA200', 93.029, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2079', 'HEA200', 93.029, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2080', 'HEA200', 193.508, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2081', 'HEA200', 133.304, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2082', 'HEA200', 133.304, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2083', 'HEA200', 122.855, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2084', 'HEA200', 122.855, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2085', 'HEA200', 389.466, 12); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2086', 'HEA200', 389.466, 12); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2087', 'HEA200', 91.138, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2088', 'HEA200', 80.599, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2089', 'HEA200', 80.599, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2090', 'HEA200', 80.599, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2091', 'HEA200', 139.013, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2092', 'HEA200', 591.006, 12); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2093', 'HEA200', 135.756, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2094', 'HEA200', 5741.136, 96); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2095', 'HEA200', 1435.296, 24); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2096', 'HEA200', 2870.232, 48); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2097', 'HEA200', 536.818, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2098', 'HEA200', 531.806, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2099', 'HEA200', 532.09, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2100', 'HEA200', 525.876, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2101', 'HEA200', 787.848, 16); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2102', 'HEA200', 1969.61, 40); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2103', 'HEA200', 928.218, 24); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2104', 'HEA200', 523.076, 10); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2105', 'HEA200', 263.449, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2106', 'HEA200', 91.138, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2107', 'HEA200', 80.599, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2108', 'HEA200', 139.013, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2109', 'HEA200', 139.013, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2110', 'HEA200', 210.068, 2); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2111', 'HEA200', 262.343, 5); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2112', 'HEA200', 141.945, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2113', 'HEA200', 141.945, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2114', 'HEA200', 151.516, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2115', 'HEA200', 151.516, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2116', 'HEA200', 141.945, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-2117', 'HEA200', 141.945, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4001', 'HEA200', 236.607, 3); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4003', 'HEA200', 235.371, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4004', 'HEA200', 235.371, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4005', 'HEA200', 235.371, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4006', 'HEA200', 235.371, 6); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4007', 'HEA200', 234.728, 7); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4008', 'HEA200', 234.728, 7); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4009', 'HEA200', 264.171, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4010', 'HEA200', 264.171, 8); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4011', 'HEA200', 645.132, 12); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4012', 'HEA200', 1290.264, 24); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4013', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4014', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4015', 'HEA200', 645.132, 12); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4016', 'HEA200', 1290.264, 24); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4017', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4018', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4019', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4020', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4021', 'HEA200', 212.603, 2); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4022', 'HEA200', 212.603, 2); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4029', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4030', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4031', 'HEA200', 215.044, 4); 
    INSERT INTO OW_SHIP_DET ( SL_PM_CODE, SL_DESC, SL_WT, SL_QTY ) VALUES ( 
    '1239606-4032', 'HEA200', 215.044, 4); 
    COMMIT;
    
    --i will check how many splits required using the following query 
    
    SELECT   sl_desc, SUM (tot_wt) t_wt, SUM (tot_qty) t_att,
             CEIL (GREATEST (SUM (tot_wt) / 50000, SUM (tot_qty) / 1000)) cnt
        FROM (SELECT   sl_desc, sl_pm_code, SUM (sl_wt) tot_wt,
                       SUM (sl_qty) tot_qty
                  FROM ow_ship_det
              GROUP BY sl_desc, sl_pm_code)
    GROUP BY sl_desc;
    
    --based on the cnt column , the sl_pm_codes for each sl_desc will get distributed
    
    SL_DESC     T_WT      T_ATT     CNT
    HEA160     14922.367       860     1
    HEA200     67216.355      2649     3
    
    --So i will have three serial numbers for HEA200 where all the sl_pm_codes will be grouped in these 3 without tampering the weight and quantities.
    --since HEA160 has 1 cnt then all the sl_pm_codes will be under 1 serial or batch number.
    
    --desired summary of  output will be 
    
    ob_DESC             ob_BATCH_NO                     sum(ob_wt)     sum(ob_qty)
    HEA160                         0001                         14,922.37      860
    HEA200                         0002                         25,318.96      989
    HEA200                         0003                        21,642.83      804
    HEA200                          0004                        20,254.56      856
  • 7. Re: Grouping reocords based on odd and even rows
    NuhaArif Newbie
    Currently Being Moderated
    hi sb92075, i managed to find out the solution in this way, kindly suggest me if there are any better ways to do this.i putting the test case again.
    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;
    
    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
    )
    
    --The procedure what i wrote is 
    
    DECLARE
       CURSOR c1
       IS
          SELECT   sl_desc, SUM (sl_wt) t_wt, SUM (sl_qty) t_att,
                   CEIL (GREATEST (SUM (sl_wt) / 50000, SUM (sl_qty) / 1000)) cnt
              FROM ow_ship_det
          GROUP BY sl_desc;
    
       CURSOR c2 (p_desc VARCHAR2)
       IS
          SELECT   sl_pm_code, sl_desc, sl_wt, sl_qty
              FROM ow_ship_det
             WHERE sl_desc = p_desc
          ORDER BY sl_wt DESC, sl_pm_code DESC;
    
       CURSOR c3
       IS
         select LPAD ( nvl (max (to_number (nvl (ob_batch, '0'))), 0)+1,4,'0')
          AS NEW_BATCH
          from   os_batch;
    
       no_of_batch_req       NUMBER;
       no_of_batch           NUMBER;
       last_batch_no         VARCHAR2 (5);
       new_batch_no          VARCHAR2 (5);
       first_batch_in_prof   VARCHAR2 (5);
    BEGIN
       IF c3%ISOPEN
       THEN
          CLOSE c3;
       END IF;
    
       OPEN c3;
    
       FETCH c3
        INTO last_batch_no;
    
       CLOSE c3;
    
       new_batch_no := last_batch_no;
       first_batch_in_prof := last_batch_no;
    
       FOR i IN c1
       LOOP
          no_of_batch_req := i.cnt;
          no_of_batch := 1;
    
          FOR j IN c2 (i.sl_desc)
          LOOP
             INSERT INTO os_batch
                         (ob_batch, ob_pm_code, ob_desc, ob_wt,
                          ob_qty
                         )
                  VALUES (new_batch_no, j.sl_pm_code, j.sl_desc, j.sl_wt,
                          j.sl_qty
                         );
    
             IF no_of_batch = no_of_batch_req
             THEN
                no_of_batch := 1;
                new_batch_no := first_batch_in_prof;
             ELSE
                no_of_batch := no_of_batch + 1;
                new_batch_no := LPAD (((new_batch_no + 1)), 4, '0');
             END IF;
          END LOOP;
    
          new_batch_no := LPAD (((first_batch_in_prof + i.cnt)), 4, '0');
          first_batch_in_prof := new_batch_no;
       END LOOP;
    
       COMMIT;
    END;
    
    OB_BATCH     OB_PM_CODE     OB_DESC     OB_WT     OB_QTY
    0001                 B               H170     15000     300
    0002                 A               H170     15000     300
    0001                 D               H170     10000     300
    0002                 C               H170     10000     300
    0003                 F               I100     25000     200
    0003                 E               I100     25000     700
    0004                 H               J100     15000     200
    0005                 G               J100     15000     200
    0004                 J               J100     11000     200
    0005                 I               J100     11000     200
  • 8. Re: Grouping reocords based on odd and even rows
    NuhaArif Newbie
    Currently Being Moderated
    hi jeneesh , please reply, as per requirement is should assign serial numbers for balancing the qty and weight in equal proportion , please check the procedure which i have attached and let me know if we can achieve the same using sql plus.
  • 9. Re: Grouping reocords based on odd and even rows
    William Robertson Oracle ACE
    Currently Being Moderated
    fyi code tags on the forum are
     - curly brackets, all-lowercase, no slashes.                                                                                                                                                                                

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points