Home » SQL & PL/SQL » SQL & PL/SQL » Help in building batch logic (Oracle 10.2.0.4.0)
Help in building batch logic [message #574814] Wed, 16 January 2013 00:29 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear All, I am trying to build one logic , there is one table with lot of item descriptions and qty , i need to split these items as batches with qty not exceeding more than 10 and with one more condition that previous description of item should not be same , this i am doing by inserting them into a different table.Kindly check below the test case along with what i tried and i am getting qty more than 10 in batch 2 as provided in test case below.


CREATE TABLE ow_stg_ship (ps_code VARCHAR2(12),ps_desc VARCHAR2(20),wt NUMBER);

CREATE TABLE OS_BTCH_ALLOC (BPS_BTCH NUMBER,BPS_CODE VARCHAR2(12),BPS_DESC VARCHAR2(20),BPS_WT NUMBER);
   
   
   INSERT INTO OW_STG_SHIP VALUES ('A','AAA',3);
   
   INSERT INTO OW_STG_SHIP VALUES ('B','AAA',5);
   
   INSERT INTO OW_STG_SHIP VALUES ('C','AAA',2);
   
   INSERT INTO OW_STG_SHIP VALUES ('D','BBB',5);
   
   INSERT INTO OW_STG_SHIP VALUES ('C','BBB',6);
   
   INSERT INTO OW_STG_SHIP VALUES ('E','BBB',2);
   
   INSERT INTO OW_STG_SHIP VALUES ('F','CCC',2);
   
   INSERT INTO OW_STG_SHIP VALUES ('G','CCC',3);


--Procedure for inserting the batches.

/* Formatted on 2013/01/16 10:09 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      SELECT   ps_code, ps_desc, wt
          FROM ow_stg_ship
      ORDER BY ps_desc ASC;

   m_prv       VARCHAR2 (50);
   m_all_wt    NUMBER;
   m_rec       NUMBER        := 0;
   tsum        NUMBER        := 0;
   tsum1       NUMBER;
   batch_cnt   NUMBER        := 1;
BEGIN
   m_all_wt := 10;
   m_prv := NULL;

   FOR i IN c1
   LOOP
      m_rec := c1%ROWCOUNT;
   END LOOP;

   FOR i IN c1
   LOOP
      tsum := tsum + i.wt;

      IF (tsum > m_all_wt AND m_prv <> i.ps_desc) OR m_rec = c1%ROWCOUNT
      THEN
         tsum1 := tsum;
      END IF;

      IF (tsum > m_all_wt AND m_prv <> i.ps_desc)
      THEN
         tsum := 0;
         batch_cnt := batch_cnt + 1;
      END IF;

      tsum1 := NULL;

      INSERT INTO os_btch_alloc
                  (bps_btch, bps_code, bps_desc, bps_wt
                  )
           VALUES (batch_cnt, i.ps_code, i.ps_desc, i.wt
                  );

      m_prv := i.ps_desc;
   END LOOP;
END;

commit;

  SELECT * FROM os_btch_alloc;

BPS_BTCH	BPS_CODE	BPS_DESC	BPS_WT
1	            A	             AAA	3
1	            B	             AAA	5
1	            C	             AAA	2
2	            D	             BBB	5
2	            E	             BBB	2
2	            C	             BBB	6
2	            F	             CCC	2
2	            G	             CCC	3


--If you see above the batch 2 has more than 10 qty, it doesnt has to be more than 10 in any case

--Desired output is

BPS_BTCH	BPS_CODE	BPS_DESC	BPS_WT
1	           A	            AAA	          3
1	           B	            AAA	          5
1	           C	            AAA	          2
2	           D	            BBB	          5
2	           E	            BBB	          2
2	           C	            BBB           3
3	           F	            CCC	          5
3	           G	            CCC	          3






[Updated on: Wed, 16 January 2013 00:42]

Report message to a moderator

Re: Help in building batch logic [message #574820 is a reply to message #574814] Wed, 16 January 2013 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have some questions:
1/ What should be the result if you remove line C/AAA?
2/ What should be the result if you swap line C/AAA and D/BBB, I mean line C has ps_code BBB and line D ps_code DDD?
3/ Why do remove 3 at line C/BBB and not line D/BBB? Or remove 1 to line E/BBB and 2 to line C/BBB? Or delete line E/BBB and remove 1 to line D/BBB? Etc. I mean what is the rule to remove bps_w?

And, for at least 100 times, ALIGN the column in the result and USE Preview button to verify!

Regards
Michel
Re: Help in building batch logic [message #574823 is a reply to message #574820] Wed, 16 January 2013 00:49 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear Michel,
THanks for the response , i have aligned the output.The logic is qty will be splited into one batch not more than 10.Kindly check now and please let me know if there is any problem.In simple each batch will have a qty 10 and not more than that until it encounters another ps_desc.


Quote:

I have some questions:
1/ What should be the result if you remove line C/AAA?


--the answer for this is ,first one i will get like this


SELECT * FROM os_btch_alloc;

BPS_BTCH	BPS_CODE	BPS_DESC	BPS_WT
1	            A	             AAA	3
1	            B	             AAA	5
2	            D	             BBB	5
2	            E	             BBB	2
2	            C	             BBB	6
2	            F	             CCC	2
2	            G	             CCC	3


--Desired output is

BPS_BTCH	BPS_CODE	BPS_DESC	BPS_WT
1	           A	            AAA	          3
1	           B	            AAA	          5
2	           D	            BBB	          5
2	           E	            BBB	          2
2	           C	            BBB           3
3	           F	            CCC	          5
3	           G	            CCC	          3


Quote:

2/ What should be the result if you swap line C/AAA and D/BBB, I mean line C has ps_code BBB and line D ps_code DDD?


--second one will be



BPS_BTCH	BPS_CODE	BPS_DESC	BPS_WT
1	            A	             AAA	3
1	            B	             AAA	5
1	            C	             BBB 	2
2	            D	             DDD	5
2	            E	             BBB	2
2	            C	             BBB	6
2	            F	             CCC	2
2	            G	             CCC	3

--Desired output will be like below.
BPS_BTCH	BPS_CODE	BPS_DESC	BPS_WT
1	            A	             AAA	3
1	            B	             AAA	5
2	            C	             BBB 	2
3	            D	             DDD	5
4	            E	             BBB	2
4	            C	             BBB	6
5	            F	             CCC	2
5	            G	             CCC	3





[Updated on: Wed, 16 January 2013 00:58]

Report message to a moderator

Re: Help in building batch logic [message #574826 is a reply to message #574823] Wed, 16 January 2013 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please answer ALL my questions.
There are 3 questions, I want 3 answers each one starting with the number of the question it refers.

Regards
Michel
Re: Help in building batch logic [message #574831 is a reply to message #574826] Wed, 16 January 2013 01:03 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks michel , actually i was editing the message and i got your response , well for the last call, well it will be

Quote:

3/ Why do remove 3 at line C/BBB and not line D/BBB? Or remove 1 to line E/BBB and 2 to line C/BBB? Or delete line E/BBB and remove 1 to line D/BBB? Etc. I mean what is the rule to remove bps_w?


--Actually michel i am not removing anything , just manipulating the data and inserting them into batches.Its like i am splitting the qty into batches with 2 conditions one is it should not exceed total of 10 and the previous description should not be same as current one , it will add up the qty till the descriptions are same and qty is not more than 10.
Re: Help in building batch logic [message #574837 is a reply to message #574831] Wed, 16 January 2013 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Point 3)

To which one do you remove some values?
What happen if you can't remove the whole number to a single row?
Can a row have a 0 value?

Quote:
, it will add up the qty till the descriptions are same and qty is not more than 10.


This assumes an order, isn't it?
From the output you gave, it seems you order the rows by BPS_CODE, is this correct?

Regards
Michel

[Updated on: Wed, 16 January 2013 01:19]

Report message to a moderator

Re: Help in building batch logic [message #574838 is a reply to message #574837] Wed, 16 January 2013 01:18 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

yes michel the order remains the same, we are not removing nor the values will be 0.

[Updated on: Wed, 16 January 2013 01:20]

Report message to a moderator

Re: Help in building batch logic [message #574839 is a reply to message #574838] Wed, 16 January 2013 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I was modifying my post, I think you didn't see it, so I repost:

Quote:
, it will add up the qty till the descriptions are same and qty is not more than 10.


This assumes an order, isn't it?
From the output you gave, it seems you order the rows by BPS_CODE, is this correct?

Regards
Michel
Re: Help in building batch logic [message #574841 is a reply to message #574839] Wed, 16 January 2013 01:25 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Quote:

This assumes an order, isn't it?
From the output you gave, it seems you order the rows by BPS_CODE, is this correct?


yes michel you are right.
Re: Help in building batch logic [message #574956 is a reply to message #574839] Thu, 17 January 2013 02:10 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Michel do you have any queris.
Re: Help in building batch logic [message #574957 is a reply to message #574956] Thu, 17 January 2013 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No and I have not the time to investigate for the moment.

Regards
Michel
Re: Help in building batch logic [message #574968 is a reply to message #574841] Thu, 17 January 2013 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
arif_md2009 wrote on Wed, 16 January 2013 08:25
Quote:

This assumes an order, isn't it?
From the output you gave, it seems you order the rows by BPS_CODE, is this correct?


yes michel you are right.


1/ ps_code is not sufficient to have a deterministic order; it should be, for instance, ps_code, ps_desc
2/ The result you gave shows you didn't shows ps_code as a order as the result should then not be:
2	           D	            BBB	          5
2	           E	            BBB	          2
2	           C	            BBB           3

but
2	           C	            BBB           6
2	           D	            BBB	          4
2	           E	            BBB	          0

So what is true?

Regards
Michel

Re: Help in building batch logic [message #574998 is a reply to message #574968] Thu, 17 January 2013 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have one more question that is not clear: does each batch contain only 1 ps_desc or can it contain several ps_desc (assuming the sum does not exceed 10)?
In short, if I add to your example:
INSERT INTO OW_STG_SHIP VALUES ('H','DDD',3);
Is it in batch 3 or is it in a batch 4?

Regards
Michel
Re: Help in building batch logic [message #575002 is a reply to message #574998] Thu, 17 January 2013 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming one ps_desc per batch, here's a solution:
SQL> select * from OW_STG_SHIP order by ps_code, ps_desc;
PS_CODE      PS_DESC                      WT
------------ -------------------- ----------
A            AAA                           3
B            AAA                           5
C            AAA                           2
C            BBB                           6
D            BBB                           5
E            BBB                           2
F            CCC                           2
G            CCC                           3
H            DDD                           3

9 rows selected.

SQL> with 
  2    data as (
  3      select ps_code, ps_desc, wt,
  4             case 
  5               when lag(ps_desc,1,'#') over (order by ps_code, ps_desc) != ps_desc
  6                 then row_number() over (order by ps_code, ps_desc)
  7             end rn
  8      from ow_stg_ship
  9    ),
 10    grouping as (
 11      select ps_code, ps_desc, wt, max(rn) over (order by ps_code, ps_desc) grp
 12      from data
 13    ),
 14    summing as (
 15      select ps_code, ps_desc, wt, 
 16             dense_rank() over (order by grp) batch_id, 
 17             row_number() over (partition by grp order by ps_code) rn,
 18             sum(wt) over (partition by grp order by ps_code) wt_sum
 19      from grouping
 20    )
 21  select batch_id, ps_code, ps_desc, wt old_wt,
 22         greatest(least(wt_sum,10)-nvl(prior wt_sum,0),0) new_wt
 23  from summing 
 24  connect by prior batch_id = batch_id and prior rn = rn - 1
 25  start with rn = 1
 26  order by batch_id, ps_code, ps_desc
 27  /
  BATCH_ID PS_CODE      PS_DESC                  OLD_WT     NEW_WT
---------- ------------ -------------------- ---------- ----------
         1 A            AAA                           3          3
         1 B            AAA                           5          5
         1 C            AAA                           2          2
         2 C            BBB                           6          6
         2 D            BBB                           5          4
         2 E            BBB                           2          0
         3 F            CCC                           2          2
         3 G            CCC                           3          3
         4 H            DDD                           3          3

9 rows selected.

Regards
Michel

[Updated on: Thu, 17 January 2013 12:32]

Report message to a moderator

Re: Help in building batch logic [message #575011 is a reply to message #574998] Thu, 17 January 2013 14:40 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks michel appreciate your great help, further to your clarifications, there will be a seperate batch for each ps_desc and it should not have 0 as qty, i think we should go with 'OR' condition instead of 'AND' satisying both the conditions.Each batch must have only one ps_desc or if its more than 10 it should be in the same batch.In case if you insert the row to the first inserts it will be in batch 3 as there is one ps_desc per batch and you are absolutely right the order is by one and only ps_desc.



Re: Help in building batch logic [message #575031 is a reply to message #575011] Fri, 18 January 2013 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what would be the result for the data you gave including the row I added and the following one?
INSERT INTO OW_STG_SHIP VALUES ('I','AAA',5);

I admit that now I no more understand the requirements. Does my query give the correct result?
Note that an order by ONLY ps_desc is not deterministic. What is the primary key of your table?

Regards
Michel
Re: Help in building batch logic [message #575071 is a reply to message #575031] Fri, 18 January 2013 22:27 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Quote:

I admit that now I no more understand the requirements. Does my query give the correct result?

thanks michel for your help, the query is working fine with one exception the weight with 0 for ps_desc 'BBB' and ps_code 'E' is wrong as the requirement is simple the qty should not be greater than 10, if it exceeds then the extra qty will be in new batch , even if the ps_Desc is same.
Quote:

Note that an order by ONLY ps_desc is not deterministic. What is the primary key of your table?


I agree with you to have ps_code included in order ,yes ps_code will be the primary key.


Quote:
So what would be the result for the data you gave including the row I added and the following one?
INSERT INTO OW_STG_SHIP VALUES ('I','AAA',5);


The required output is if it exceeds then the extra qty will be in new batch , even if the ps_Desc is same , like after adding the last insert you gave me.




SQL> INSERT INTO OW_STG_SHIP VALUES ('I','AAA',5);

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  with 
  2      data as (
  3          select ps_code, ps_desc, wt,
  4                 case 
  5                   when lag(ps_desc,1,'#') over (order by ps_code, ps_desc) != ps_desc
  6                     then row_number() over (order by ps_code, ps_desc)
  7                 end rn
  8          from ow_stg_ship
  9        ),
 10       grouping as (
 11         select ps_code, ps_desc, wt, max(rn) over (order by ps_code, ps_desc) grp
 12         from data    ),
 13       summing as (
 14         select ps_code, ps_desc, wt, 
 15                dense_rank() over (order by grp) batch_id,  
 16                row_number() over (partition by grp order by ps_code) rn,
 17                sum(wt) over (partition by grp order by ps_code) wt_sum
 18         from grouping
 19       )
 20     select batch_id, ps_code, ps_desc, wt old_wt,
 21            greatest(least(wt_sum,10)-nvl(prior wt_sum,0),0) new_wt
 22     from summing 
 23     connect by prior batch_id = batch_id and prior rn = rn - 1  start with rn = 1
 24*    order by batch_id, ps_code, ps_desc
 25  /

  BATCH_ID PS_CODE      PS_DESC                  OLD_WT     NEW_WT              
---------- ------------ -------------------- ---------- ----------              
         1 A            AAA                           3          3              
         1 B            AAA                           5          5              
         1 C            AAA                           2          2              
         2 C            BBB                           6          6              
         2 D            BBB                           5          4              
         2 E            BBB                           2          0              
         3 F            CCC                           2          2              
         3 G            CCC                           3          3              
         4 H            DDD                           3          3              
         5 I            AAA                           5          5              

10 rows selected.

SQL> spool off;

--what i need is similar to I or last row.total of six batches will be generated.

  BATCH_ID PS_CODE      PS_DESC                  OLD_WT     NEW_WT              
---------- ------------ -------------------- ---------- ----------              
         1 A            AAA                           3          3              
         1 B            AAA                           5          5              
         1 C            AAA                           2          2              
         2 C            BBB                           6          6              
         2 D            BBB                           5          4              
         3 E            BBB                           2          3             
         4 F            CCC                           2          2              
         4 G            CCC                           3          3              
         5 H            DDD                           3          3              
         6 I            AAA                           5          5              



Re: Help in building batch logic [message #575077 is a reply to message #575071] Sat, 19 January 2013 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the query is working fine with one exception the weight with 0 for ps_desc 'BBB' and ps_code 'E' is wrong as the requirement is simple the qty should not be greater than 10, if it exceeds then the extra qty will be in new batch


This is NOT what you have said.
If you change the requirements at each post we will not have a correct query, NEVER.

Regards
Michel
Re: Help in building batch logic [message #575082 is a reply to message #575077] Sat, 19 January 2013 01:01 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I am sorry michel i am not changing the requirement as this is important for us if you see my very first post.Many thanks for the help you provided so far , as i am able to reach to this position, appreciate if you have time to consider this .
Re: Help in building batch logic [message #575083 is a reply to message #575082] Sat, 19 January 2013 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if you see my very first post


Yes, if I see your very first post, I see:
original values
2	            D	             BBB	5
2	            E	             BBB	2
2	            C	             BBB	6

final values
2	           D	            BBB	          5
2	           E	            BBB	          2
2	           C	            BBB           3

So you modify the values to not exceed 10 and our later roundtrips you talk about changing values to 0 but not removing rows, and so on.

In addition you said:

Quote:
with one more condition that previous description of item should not be same


So your late result:

Quote:
         2 C            BBB                           6          6              
         2 D            BBB                           5          4              
         3 E            BBB                           2          3             

is wrong as batch 3 has the same ps_desc than the previous batch.

In the end, you move quantity from one row to another one.
This is a COMPLETELY different problem.
And I have a question for it: what happen if you change wt from 2 to 10 in row E/BBB?

Regards
Michel

[Updated on: Sat, 19 January 2013 01:19]

Report message to a moderator

Re: Help in building batch logic [message #575101 is a reply to message #575083] Sat, 19 January 2013 05:00 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i am really sorry michel for changing the requirement , but can we change the logic a bit by removing the 0.
Re: Help in building batch logic [message #575102 is a reply to message #575101] Sat, 19 January 2013 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 11g with recursive query it is very easy to solve your problem.
In 10g it is harder, you can apply the same kind logic but exactly the same one. As I said you do not just modify a row but subtract from a row and add to another one.

And you DID NOT answer my question which a VERY important one, without an answer you cannot find a query as it is a KEY point of the requirement that determines if your requirements are possible/consistent or not whatever the way you want to implement them.

Regards
Michel

[Updated on: Sat, 19 January 2013 05:59]

Report message to a moderator

Re: Help in building batch logic [message #575118 is a reply to message #575102] Sat, 19 January 2013 12:11 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks michel for the solution, You are amazing,i have disturbed you a lot by not specifying the problem correctly , actually whenever the qty is greater than 10 or equal to 10 it goes to another batch or create a new batch to add those extra qty > 10.even if the ps_desc is same, if its harder to resolve in 10g by query then can i consider writing a procedure.Well the answer for your query is

Quote:

And I have a question for it: what happen if you change wt from 2 to 10 in row E/BBB?


the output will be


--based on this i will answer your query
         2 C            BBB                           6                       
         2 D            BBB                           5                       
         3 E            BBB                           2 

--the output what i want is as below since each batch should not exceed 10.                       

         2 C            BBB                           6                        
         2 D            BBB                           4 
         3 E            BBB                           3 New batch will be crtd since >10) 
         4 E            BBB                           10 it will add new batch 4 since =10

        
Re: Help in building batch logic [message #575121 is a reply to message #575118] Sat, 19 January 2013 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't understand what I meant.
You remove E/BBB/3 and replace it by E/BBB/10, what is the result now?

To be easier I repost the complete test case:
drop table ow_stg_ship;
CREATE TABLE ow_stg_ship (ps_code VARCHAR2(7),ps_desc VARCHAR2(7),wt NUMBER);
   INSERT INTO OW_STG_SHIP VALUES ('A','AAA',3);
   INSERT INTO OW_STG_SHIP VALUES ('B','AAA',5);
   INSERT INTO OW_STG_SHIP VALUES ('C','AAA',2);
   INSERT INTO OW_STG_SHIP VALUES ('D','BBB',5);
   INSERT INTO OW_STG_SHIP VALUES ('C','BBB',6);
   INSERT INTO OW_STG_SHIP VALUES ('E','BBB',10);
   INSERT INTO OW_STG_SHIP VALUES ('F','CCC',2);
   INSERT INTO OW_STG_SHIP VALUES ('G','CCC',3);
   INSERT INTO OW_STG_SHIP VALUES ('H','DDD',3);
   INSERT INTO OW_STG_SHIP VALUES ('I','AAA',5);
commit;

Regards
Michel
Re: Help in building batch logic [message #575132 is a reply to message #575121] Sat, 19 January 2013 22:26 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi michel, thanks for the great help sir, i recreated those table and inserted values , i will post what i need after the result what i am getting from your solution.This is final rquirement.


SQL> drop table ow_stg_ship;

Table dropped.

SQL> CREATE TABLE ow_stg_ship (ps_code VARCHAR2(7),ps_desc VARCHAR2(7),wt NUMBER);

Table created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('A','AAA',3);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('B','AAA',5);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('C','AAA',2);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('D','BBB',5);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('C','BBB',6);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('E','BBB',10);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('F','CCC',2);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('G','CCC',3);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('H','DDD',3);

1 row created.

SQL>    INSERT INTO OW_STG_SHIP VALUES ('I','AAA',5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ow_stg_ship;

PS_CODE PS_DESC         WT                                                      
------- ------- ----------                                                      
A       AAA              3                                                      
B       AAA              5                                                      
C       AAA              2                                                      
D       BBB              5                                                      
C       BBB              6                                                      
E       BBB             10                                                      
F       CCC              2                                                      
G       CCC              3                                                      
H       DDD              3                                                      
I       AAA              5                                                      

10 rows selected.

SQL> with
  2        data as (
  3          select ps_code, ps_desc, wt,
  4                 case
  5                   when lag(ps_desc,1,'#') over (order by ps_code, ps_desc) != ps_desc
  6                     then row_number() over (order by ps_code, ps_desc)
  7                 end rn
  8          from ow_stg_ship
  9        ),
 10       grouping as (
 11         select ps_code, ps_desc, wt, max(rn) over (order by ps_code, ps_desc) grp
 12         from data
 13       ),
 14       summing as (
 15         select ps_code, ps_desc, wt,
 16                dense_rank() over (order by grp) grp,
 17                row_number() over (partition by grp order by ps_code) rn,
 18                sum(wt) over (partition by grp order by ps_code) wt_sum
 19         from grouping
 20       )
 21     select grp batch_id, ps_code, ps_desc, wt old_wt,
 22            greatest(least(wt_sum,10)- nvl(prior wt_sum,0),0) new_wt
 23     from summing
 24     connect by prior grp = grp and prior rn = rn - 1
 25     start with rn = 1
 26     order by batch_id, ps_code, ps_desc;

  BATCH_ID PS_CODE PS_DESC     OLD_WT     NEW_WT                                
---------- ------- ------- ---------- ----------                                
         1 A       AAA              3          3                                
         1 B       AAA              5          5                                
         1 C       AAA              2          2                                
         2 C       BBB              6          6                                
         2 D       BBB              5          4                                
         2 E       BBB             10          0                                
         3 F       CCC              2          2                                
         3 G       CCC              3          3                                
         4 H       DDD              3          3                                
         5 I       AAA              5          5                                

10 rows selected.


--What i want is as below



  BATCH_ID PS_CODE PS_DESC     OLD_WT     NEW_WT                                
---------- ------- ------- ---------- ----------                                
         1 A       AAA              3          3                                
         1 B       AAA              5          5                                
         1 C       AAA              2          2                                
         2 C       BBB              6          6                                
         2 D       BBB              5          4                                
         3 D       BBB              5          1
         4 E       BBB             10          10                                
         5 F       CCC              2          2                                
         5 G       CCC              3          3                                
         6 H       DDD              3          3                                
         7 I       AAA              5          5 



I will explain, since batch 2 was having 10 pcs (6+4), the remaining qty will go into as new batch 3 , and for 10 pcs a new batch will be created as 4.Batch 5 can accomodate more 5 pcs of CCC ,batch 6 can have 7 of DDD and batch 7 can have 5 more of AAA Ps_desc.I hope this clarifies you michel, i am extremely sorry for not explaining it you properly earlier.Thanks very much for your great help.




[Updated on: Sat, 19 January 2013 22:28]

Report message to a moderator

Re: Help in building batch logic [message #575135 is a reply to message #575132] Sun, 20 January 2013 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you can add new rows!?

Regards
Michel

[Updated on: Sun, 20 January 2013 00:48]

Report message to a moderator

Re: Help in building batch logic [message #575136 is a reply to message #575135] Sun, 20 January 2013 00:52 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

yes michel.
Re: Help in building batch logic [message #575137 is a reply to message #575136] Sun, 20 January 2013 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, original and final result do not match and it does not matter how the "wt" are spread in the final result set.
What I meant is that it does matter if the result is:
         2 C       BBB              6          6                                
         2 D       BBB              5          4                                
         3 D       BBB              5          1
         4 E       BBB             10          10            

or
         2 C       BBB              6          5                                
         2 D       BBB              5          5                                
         3 D       BBB              5          1
         4 E       BBB             10          10            

or
         2 C       BBB              6          4                                
         2 D       BBB              5          3                                
         3 D       BBB              5          3
         4 E       BBB             10          10            

Isn't it?
If yes, is the following allowed?
         2 C       BBB              6          4                                
         2 D       BBB              5          3                                
         3 D       BBB              5          7
         4 E       BBB             10          7            

If not, why?

Regards
Michel
Re: Help in building batch logic [message #575138 is a reply to message #575137] Sun, 20 January 2013 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also you added a row:
         3 D       BBB              5          1

Why is this not:
         3 E       BBB              5          1


Regards
Michek
Re: Help in building batch logic [message #575139 is a reply to message #575137] Sun, 20 January 2013 01:27 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks michel, first two cases in your or are ok but in case of 3 and 4 its no because each batch can have 10pcs and if you see batch 2,it will have only 7 whereas it accommodate 10 pcs in it.


Quote:


or
2 C BBB 6 4
2 D BBB 5 3
3 D BBB 5 3
4 E BBB 10 10

Isn't it?
If yes, is the following allowed?
2 C BBB 6 4
2 D BBB 5 3
3 D BBB 5 7
4 E BBB 10 7

Re: Help in building batch logic [message #575140 is a reply to message #575138] Sun, 20 January 2013 01:28 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i took the sequence in order since D was in order and it had remaining balance so the balance qty belonged to d.

[Updated on: Sun, 20 January 2013 01:29]

Report message to a moderator

Re: Help in building batch logic [message #575144 is a reply to message #575139] Sun, 20 January 2013 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
thanks michel, first two cases in your or are ok but in case of 3 and 4 its no because each batch can have 10pcs and if you see batch 2,it will have only 7 whereas it accommodate 10 pcs in it.


So why
         2 C       BBB              6                                
         2 D       BBB              4                                
         3 D       BBB              1
         4 E       BBB             10

is not
         2 C       BBB              6                                
         2 D       BBB              4                                
         3 E       BBB             10
         4 E       BBB              1

Removing the original values as they are meaningless and irrelevant.
Actually, I can't think the added rows can be anywhere but at the end (of the subset).

Regards
Michel

[Updated on: Sun, 20 January 2013 02:37]

Report message to a moderator

Re: Help in building batch logic [message #575150 is a reply to message #575144] Sun, 20 January 2013 03:58 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Yes ofcourse , we can have it either ways in this manner.
Re: Help in building batch logic [message #575151 is a reply to message #575144] Sun, 20 January 2013 05:09 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

both cases are acceptable as long as batch is lesser or equal to 10 pcs.
Re: Help in building batch logic [message #575152 is a reply to message #575151] Sun, 20 January 2013 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, it is possible in SQL but it requires to use MODEL clause. You can easily do it in PL/SQL (pipelined function if you want to keep the SELECT form).
I have no time for the moment to post a complete solution (it is lunch time for us, and even Sunday lunch time which, for French, means something that will last several hours).

Regards
Michel
Re: Help in building batch logic [message #575156 is a reply to message #575152] Sun, 20 January 2013 07:53 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks michel, Please go ahead and enjoy your French lunch, in meantime i will try to browse more articles on model clause and pipelined function as i remember our great barbara has once given me a solution using pipelined funtion.i will try to use the method.
Re: Help in building batch logic [message #575206 is a reply to message #575156] Mon, 21 January 2013 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's an example of what you can do in PL/SQL to fill your target table.
Test case:
drop table ow_stg_ship;
CREATE TABLE ow_stg_ship (ps_code VARCHAR2(7),ps_desc VARCHAR2(7),wt NUMBER);
   INSERT INTO OW_STG_SHIP VALUES ('A','AAA',3);
   INSERT INTO OW_STG_SHIP VALUES ('B','AAA',5);
   INSERT INTO OW_STG_SHIP VALUES ('C','AAA',2);
   INSERT INTO OW_STG_SHIP VALUES ('D','BBB',5);
   INSERT INTO OW_STG_SHIP VALUES ('C','BBB',6);
   INSERT INTO OW_STG_SHIP VALUES ('E','BBB',10);
   INSERT INTO OW_STG_SHIP VALUES ('F','CCC',2);
   INSERT INTO OW_STG_SHIP VALUES ('G','CCC',3);
   INSERT INTO OW_STG_SHIP VALUES ('H','DDD',3);
   INSERT INTO OW_STG_SHIP VALUES ('I','AAA',5);
   INSERT INTO OW_STG_SHIP VALUES ('J','AAA',7);
commit;


Filling the target table:
SQL> declare
  2    batch_id pls_integer              := 1;
  3    cur_code ow_stg_ship.ps_code%type := '';
  4    cur_desc ow_stg_ship.ps_desc%type := '';
  5    cur_wt   ow_stg_ship.wt%type      := 0;  -- Current total of wt in batch
  6    remain   pls_integer              := 0;
  7  begin
  8    for rec in (select ps_code, ps_desc, wt from ow_stg_ship order by ps_code, ps_desc) loop
  9       if rec.ps_desc != cur_desc then
 10         if remain = 0 then 
 11           batch_id := batch_id + 1; 
 12         else
 13           while remain > 0 loop
 14             -- Generate rows for remaining wt
 15             insert into os_btch_alloc (bps_btch, bps_code, bps_desc, bps_wt)
 16             values (batch_id, cur_code, cur_desc, least(10, remain));
 17             remain := remain - 10;
 18             batch_id := batch_id + 1; 
 19           end loop;
 20         end if;
 21         remain := 0;
 22         cur_wt := 0;
 23       end if;
 24       -- Generate target row for current original row
 25       cur_code := rec.ps_code;
 26       cur_desc := rec.ps_desc;
 27       insert into os_btch_alloc (bps_btch, bps_code, bps_desc, bps_wt)
 28       values (batch_id, cur_code, cur_desc, least(remain+rec.wt, 10-cur_wt));
 29       cur_wt := cur_wt + remain + rec.wt;
 30       if cur_wt > 10 then 
 31         batch_id := batch_id + 1; 
 32         remain   := cur_wt - 10;
 33         cur_wt   := 0;
 34       else
 35         remain := 0;
 36       end if;
 37    end loop;
 38    -- if some wt remains, generate the rows for it
 39    while remain > 0 loop
 40      insert into os_btch_alloc (bps_btch, bps_code, bps_desc, bps_wt)
 41      values (batch_id, cur_code, cur_desc, least(10, remain));
 42      remain   := remain - 10;
 43      batch_id := batch_id + 1;
 44    end loop;
 45  end;
 46  /

PL/SQL procedure successfully completed.

SQL> select * from os_btch_alloc order by bps_btch, bps_code;
  BPS_BTCH BPS_CODE     BPS_DESC                 BPS_WT
---------- ------------ -------------------- ----------
         1 A            AAA                           3
         1 B            AAA                           5
         1 C            AAA                           2
         2 C            BBB                           6
         2 D            BBB                           4
         3 E            BBB                          10
         4 E            BBB                           1
         5 F            CCC                           2
         5 G            CCC                           3
         6 H            DDD                           3
         7 I            AAA                           5
         7 J            AAA                           5
         8 J            AAA                           2

13 rows selected.

SQL> select * from OW_STG_SHIP order by ps_code, ps_desc;
PS_CODE PS_DESC         WT
------- ------- ----------
A       AAA              3
B       AAA              5
C       AAA              2
C       BBB              6
D       BBB              5
E       BBB             10
F       CCC              2
G       CCC              3
H       DDD              3
I       AAA              5
J       AAA              7

11 rows selected.

Regards
Michel
icon14.gif  Re: Help in building batch logic [message #575216 is a reply to message #575206] Mon, 21 January 2013 06:06 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

One million thanks michel , this is what exactly i want.You are great.I was just pondering on model clause and was reading on it and I found them a bit difficult to understand. can u suggest me some articles on it so that i can read.Again thanks a lot for your time and great help to me.
Re: Help in building batch logic [message #575232 is a reply to message #575216] Mon, 21 January 2013 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No I don't know documentation to easily learn MODEL clause.
There is a chapter in Database Data Warehousing Guide: Chapter 22 SQL for Modeling

Some solutions in our Puzzles are given with MODEL clause.

And there are a couple of simple articles on OTN.

Regards
Michel
icon14.gif  Re: Help in building batch logic [message #575283 is a reply to message #575232] Mon, 21 January 2013 21:57 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks a lot michel.I will go through them, i need to read more on Hierarchical queries then analytical functions and then i believe should read on this sql modeling clause.
Previous Topic: Join two permanent tables with condition
Next Topic: External Tables (Oracle Loader)
Goto Forum:
  


Current Time: Wed Dec 17 15:33:54 CST 2014

Total time taken to generate the page: 0.14298 seconds