Home » SQL & PL/SQL » SQL & PL/SQL » How to use Row Generator (Oracle 11g R2,RHEL 5.0)
How to use Row Generator [message #582222] Tue, 16 April 2013 01:40 Go to next message
jimit_shaili
Messages: 207
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

I have a specific problem like below of allocating proper pallets to received quantity. Herewith I'm posting required test data. Please suggest me how solve below problem with row generator query feature.

CREATE TABLE PALLETMASTER
(
  PALLETTYPE  VARCHAR2(1 BYTE),
  CAPACITY    NUMBER(2)
);

Insert into PALLETMASTER   (PALLETTYPE, CAPACITY) Values   ('A', 18);
Insert into PALLETMASTER   (PALLETTYPE, CAPACITY) Values   ('B', 16);
Insert into PALLETMASTER   (PALLETTYPE, CAPACITY) Values   ('C', 15);
Insert into PALLETMASTER   (PALLETTYPE, CAPACITY) Values   ('D', 14);

COMMIT;

CREATE TABLE INWARDMASTER
(
  ITEMCODE    VARCHAR2(4 BYTE),
  PALLETTYPE  VARCHAR2(1 BYTE),
  INWARD_QTY  NUMBER(4)
);

Insert into INWARDMASTER   (ITEMCODE, PALLETTYPE, INWARD_QTY) Values   ('XA1', 'A', 40);
Insert into INWARDMASTER   (ITEMCODE, PALLETTYPE, INWARD_QTY) Values   ('XB2', 'A', 15);
Insert into INWARDMASTER   (ITEMCODE, PALLETTYPE, INWARD_QTY) Values   ('XC3', 'B', 10);
Insert into INWARDMASTER   (ITEMCODE, PALLETTYPE, INWARD_QTY) Values   ('XD4', 'C', 35);
Insert into INWARDMASTER   (ITEMCODE, PALLETTYPE, INWARD_QTY) Values   ('XE5', 'D', 14);
COMMIT;

SQL> select a.itemcode,a.inward_qty,a.pallettype,b.capacity
  2  from inwardmaster a left join palletmaster b on
  3  b.pallettype = a.pallettype
  4  order by a.itemcode
  5  /

ITEM INWARD_QTY P   CAPACITY
---- ---------- - ----------
XA1          40 A         18
XB2          15 A         18
XC3          10 B         16
XD4          35 C         15
XE5          14 D         14


As if for itemcode 'XA1' pallettype is 'A' witch capacity is 18 and inward quantity is 40, so required
3 palletes of 'A' type for that item and so on.
I required result set like below.

ITEM INWARD_QTY P   CAPACITY	ALLOCATION   PSRL
---- ---------- - ----------	----------   ----
XA1          40 A         18		18	1
XA1          40 A         18		18	2
XA1          40 A         18		4	3

XB2          15 A         18		15      1

XC3          10 B         16		10	1

XD4          35 C         15		15	1
XD4          35 C         15		15	2
XD4          35 C         15		5	3

XE5          14 D         14		14	1


Thanks & Regards

Jimit
Re: How to use Row Generator [message #582228 is a reply to message #582222] Tue, 16 April 2013 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You did the first step, now the row generator one (and a little bit of math):
SQL> break on itemcode skip 1 dup
SQL> with
  2    data as (
  3      select a.itemcode, a.inward_qty, a.pallettype, b.capacity
  4      from inwardmaster a join palletmaster b on b.pallettype = a.pallettype
  5    )
  6  select itemcode, inward_qty, pallettype, capacity,
  7         least(capacity, inward_qty-(column_value-1)*capacity) allocation,
  8         column_value psrl
  9  from data,
 10       table(cast(multiset(select level from dual
 11                           connect by level <= ceil(inward_qty/capacity))
 12                  as sys.odciNumberList))
 13  order by itemcode, psrl
 14  /
ITEM INWARD_QTY P   CAPACITY ALLOCATION       PSRL
---- ---------- - ---------- ---------- ----------
XA1          40 A         18         18          1
XA1          40 A         18         18          2
XA1          40 A         18          4          3

XB2          15 A         18         15          1

XC3          10 B         16         10          1

XD4          35 C         15         15          1
XD4          35 C         15         15          2
XD4          35 C         15          5          3

XE5          14 D         14         14          1

Regards
Michel
Re: How to use Row Generator [message #582229 is a reply to message #582228] Tue, 16 April 2013 03:31 Go to previous messageGo to next message
pointers
Messages: 336
Registered: May 2008
Senior Member
@Micheal,

This was very nice code.

Thanks.

Regards,
Pointers
Re: How to use Row Generator [message #582314 is a reply to message #582229] Wed, 17 April 2013 00:54 Go to previous messageGo to next message
jimit_shaili
Messages: 207
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks Micheal for as always a great response.

I've tried below query to clear my own logic about analytic queries and seems to be succeeded but still there are much more space for improvement. It looks like specific solution to a given situation and may not be work as generic usage. So please help me to remove complexity from below query, although it is giving desired result.

My Try....
SQL> select 
  2  itemcode, inward_qty, pallettype, capacity
  3  ,case 
  4  when qty is null and capacity < inward_qty then capacity 
  5  when qty is null and capacity >= inward_qty then inward_qty
  6  when qty is not null and qty <= capacity then capacity 
  7  when qty is not null and qty > capacity then inward_qty - qty
  8  else inward_qty - qty end allocation
  9  ,psrl from
 10  (
 11   select lag(cumu_qty) over (partition by itemcode order by itemcode,psrl) qty,itemcode, inward_qty, pallettype, capacity,psrl from
 12   (
 13    select sum(sum(capacity)) over(partition by itemcode order by itemcode,psrl) cumu_qty,itemcode, inward_qty, pallettype, capacity,psrl from
 14    (
 15     select distinct itemcode, inward_qty, pallettype, capacity,psrl from
 16     (
 17      select itemcode, inward_qty, pallettype, capacity,level psrl from
 18      (
 19       select a.itemcode, a.inward_qty, a.pallettype, b.capacity,ceil(a.inward_qty/b.capacity) allocation from inwardmaster a join palletmaster b on b.pallettype = a.pallettype
 20      ) gen connect by level <=allocation
 21     ) src
 22    ) group by inward_qty,itemcode, inward_qty, pallettype, capacity,psrl
 23   )
 24  )  
 25  order by itemcode
 26  ;

ITEM INWARD_QTY P   CAPACITY ALLOCATION       PSRL
---- ---------- - ---------- ---------- ----------
XA1          40 A         18         18          1
XA1          40 A         18         18          2
XA1          40 A         18          4          3
XB2          15 A         18         15          1
XC3          10 B         16         10          1
XD4          35 C         15         15          1
XD4          35 C         15         15          2
XD4          35 C         15          5          3
XE5          14 D         14         14          1

9 rows selected.


Thanks & Regards

Jimit
Re: How to use Row Generator [message #582315 is a reply to message #582314] Wed, 17 April 2013 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First you have to rewrite it using the WITH clause and split lines.
Inline view of inline view of inline view... all written in a single line is unreadable.

Also explain what your query intends to do.

Regards
Michel
Re: How to use Row Generator [message #582316 is a reply to message #582315] Wed, 17 April 2013 01:13 Go to previous messageGo to next message
jimit_shaili
Messages: 207
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Micheal

Hope this will satisfy your requirement and i'm not much used to WITH clause queries.

select itemcode, inward_qty, pallettype, capacity
,case
when qty is null and capacity < inward_qty then capacity
when qty is null and capacity >= inward_qty then inward_qty
when qty is not null and qty <= capacity then capacity
when qty is not null and qty > capacity then inward_qty - qty
else inward_qty - qty end allocation
,psrl from(
select lag(cumu_qty) over (partition by itemcode order by itemcode,psrl) qty
,itemcode, inward_qty, pallettype, capacity,psrl from(
	select sum(sum(capacity)) over(partition by itemcode order by itemcode,psrl) cumu_qty
	,itemcode, inward_qty, pallettype, capacity,psrl from(
		select distinct itemcode, inward_qty, pallettype, capacity,psrl from(
			select itemcode, inward_qty, pallettype, capacity,level psrl from(
				select a.itemcode, a.inward_qty, a.pallettype, b.capacity
				,ceil(a.inward_qty/b.capacity) allocation from
				inwardmaster a join palletmaster b on 
				b.pallettype = a.pallettype
			) gen	connect by level <=allocation
		) src
	)	group by inward_qty,itemcode, inward_qty, pallettype, capacity,psrl
)
) order by itemcode
Re: How to use Row Generator [message #582318 is a reply to message #582316] Wed, 17 April 2013 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
First you have to rewrite it using the WITH clause

And to format use SQL Formatter.

Regards
Michel
Re: How to use Row Generator [message #582323 is a reply to message #582318] Wed, 17 April 2013 03:38 Go to previous messageGo to next message
jimit_shaili
Messages: 207
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Sorry i don't know how to use with clause in query. Please help me out.

SELECT itemcode,
       inward_qty,
       pallettype,
       capacity,
       CASE
         WHEN qty IS NULL
              AND capacity < inward_qty THEN capacity
         WHEN qty IS NULL
              AND capacity >= inward_qty THEN inward_qty
         WHEN qty IS NOT NULL
              AND qty <= capacity THEN capacity
         WHEN qty IS NOT NULL
              AND qty > capacity THEN inward_qty - qty
         ELSE inward_qty - qty
       END allocation,
       psrl
FROM  (SELECT Lag(cumu_qty)
                over (
                  PARTITION BY itemcode
                  ORDER BY itemcode, psrl) qty,
              itemcode,
              inward_qty,
              pallettype,
              capacity,
              psrl
       FROM  (SELECT SUM(SUM(capacity))
                       over(
                         PARTITION BY itemcode
                         ORDER BY itemcode, psrl) cumu_qty,
                     itemcode,
                     inward_qty,
                     pallettype,
                     capacity,
                     psrl
              FROM  (SELECT DISTINCT itemcode,
                                     inward_qty,
                                     pallettype,
                                     capacity,
                                     psrl
                     FROM  (SELECT itemcode,
                                   inward_qty,
                                   pallettype,
                                   capacity,
                                   LEVEL psrl
                            FROM  (SELECT a.itemcode,
                                          a.inward_qty,
                                          a.pallettype,
                                          b.capacity,
                                          Ceil(a.inward_qty / b.capacity)
                                          allocation
                                   FROM   inwardmaster a
                                          join palletmaster b
                                            ON b.pallettype = a.pallettype) gen
                            CONNECT BY LEVEL <= allocation) src)
              GROUP  BY inward_qty,
                        itemcode,
                        inward_qty,
                        pallettype,
                        capacity,
                        psrl))
ORDER  BY itemcode  


Thanks & Regards

Jimit
Re: How to use Row Generator [message #582325 is a reply to message #582323] Wed, 17 April 2013 03:55 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
WITH <subquery name> AS (<subquery>),
<subquery name2> AS (<subquery2>),
.....
SELECT * FROM <one or more with clause subquery names>

So:
WITH gen AS (SELECT a.itemcode,
                                          a.inward_qty,
                                          a.pallettype,
                                          b.capacity,
                                          Ceil(a.inward_qty / b.capacity)
                                          allocation
                                   FROM   inwardmaster a
                                          join palletmaster b
                                            ON b.pallettype = a.pallettype),
src AS (SELECT itemcode,
                                   inward_qty,
                                   pallettype,
                                   capacity,
                                   LEVEL psrl
                            FROM gen
                            CONNECT BY LEVEL <= allocation),
.....

See if you can fill in the rest yourself.
Re: How to use Row Generator [message #582327 is a reply to message #582325] Wed, 17 April 2013 04:19 Go to previous messageGo to next message
jimit_shaili
Messages: 207
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks cookiemonster

Below one is my try, totally inspired by your suggestive query. I think this will satisfy your and Michel's requirement also. So please help me out to reduce complexity of query.

WITH gen
     AS (SELECT a.itemcode,
                a.inward_qty,
                a.pallettype,
                b.capacity,
                Ceil(a.inward_qty / b.capacity) allocation
         FROM   inwardmaster a
                left join palletmaster b
                       ON b.pallettype = a.pallettype),
     src
     AS (SELECT itemcode,
                inward_qty,
                pallettype,
                capacity,
                LEVEL psrl
         FROM   gen
         CONNECT BY LEVEL <= allocation),
     dis
     AS (SELECT DISTINCT itemcode,
                         inward_qty,
                         pallettype,
                         capacity,
                         psrl
         FROM   src),
     cumu
     AS (SELECT SUM(SUM(capacity))
                  over(
                    PARTITION BY itemcode
                    ORDER BY itemcode, psrl) cumu_qty,
                itemcode,
                inward_qty,
                pallettype,
                capacity,
                psrl
         FROM   dis
         GROUP  BY inward_qty,
                   itemcode,
                   inward_qty,
                   pallettype,
                   capacity,
                   psrl),
     anal1
     AS (SELECT Lag(cumu_qty)
                  over (
                    PARTITION BY itemcode
                    ORDER BY itemcode, psrl) qty,
                itemcode,
                inward_qty,
                pallettype,
                capacity,
                psrl
         FROM   cumu),
     anal2
     AS (SELECT itemcode,
                inward_qty,
                pallettype,
                capacity,
                CASE
                  WHEN qty IS NULL
                       AND capacity < inward_qty THEN capacity
                  WHEN qty IS NULL
                       AND capacity >= inward_qty THEN inward_qty
                  WHEN qty IS NOT NULL
                       AND qty <= capacity THEN capacity
                  WHEN qty IS NOT NULL
                       AND qty > capacity THEN inward_qty - qty
                  ELSE inward_qty - qty
                END allocation,
                psrl
         FROM   anal1)
SELECT *
FROM   anal2
ORDER  BY itemcode

ITEM INWARD_QTY P   CAPACITY ALLOCATION       PSRL
---- ---------- - ---------- ---------- ----------
XA1          40 A         18         18          1
XA1          40 A         18         18          2
XA1          40 A         18          4          3
XB2          15 A         18         15          1
XC3          10 B         16         10          1
XD4          35 C         15         15          1
XD4          35 C         15         15          2
XD4          35 C         15          5          3
XE5          14 D         14         14          1

9 rows selected.
  


Thanks & Regards

Jimit
Re: How to use Row Generator [message #582329 is a reply to message #582327] Wed, 17 April 2013 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the problem with mine?

Regards
Michel
Re: How to use Row Generator [message #582333 is a reply to message #582329] Wed, 17 April 2013 04:38 Go to previous messageGo to next message
jimit_shaili
Messages: 207
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

No problem at all, with your suggestive query. I'm just trying to learn SQL analytic query and developed the solution but it looks much complex then yours. That's why I'm asking the experts, is there any improvement or complexity reduction possible?

Thanks & Regards

Jimit
Re: How to use Row Generator [message #582339 is a reply to message #582333] Wed, 17 April 2013 05:26 Go to previous message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to tell us the rationales that make you write this particular query.
How did you imagine it? How did you build it?

Regards
Michel
Previous Topic: updating the old values in trigger
Next Topic: how to update column value with out using sequences
Goto Forum:
  


Current Time: Sat Apr 19 11:26:42 CDT 2014

Total time taken to generate the page: 0.11470 seconds