OraFAQ Forum: SQL & PL/SQL » Help Required In

Home » SQL & PL/SQL » SQL & PL/SQL » Help Required In - Row Generator Query (Oracle 11gR2, 11.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help Required In - Row Generator Query [message #622101]

Thu, 21 August 2014 02:10

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Friends,

Below is the script for test case.

CREATE TABLE TEMP
(
  SRNO			NUMBER(8),
  SRL				NUMBER(8),
  BAG_START	NUMBER(8),
  BAG_END		NUMBER(8)
);

Insert into TEMP   (SRNO, SRL, BAG_START, BAG_END) Values   (1, 1, 1, 5);
Insert into TEMP   (SRNO, SRL, BAG_START, BAG_END) Values   (2, 1, 101, 110);
Insert into TEMP   (SRNO, SRL, BAG_START, BAG_END) Values   (2, 2, 111, 120);
Insert into TEMP   (SRNO, SRL, BAG_START, BAG_END) Values   (3, 1, 1001, 1500);
Insert into TEMP   (SRNO, SRL, BAG_START, BAG_END) Values   (3, 2, 1501, 2000);

select srno,srl,level+bag_start num from (select srno,srl,(bag_start-1) bag_start,bag_end from temp where srno = 2) gen
connect by level+bag_start <= gen.bag_end
group by srno,srl,(level+bag_start)
order by srno,srl,(level+bag_start);

      SRNO        SRL        NUM
---------- ---------- ----------
         2          1        101
         2          1        102
         2          1        103
         2          1        104
         2          1        105
         2          1        106
         2          1        107
         2          1        108
         2          1        109
         2          1        110
         2          2        111
         2          2        112
         2          2        113
         2          2        114
         2          2        115
         2          2        116
         2          2        117
         2          2        118
         2          2        119
         2          2        120

In above case it works fine, untill the bag_start and bag_end range is small. As in above query it works for srno = 2, as soon as i run above query for srno = 3, it takes too long to respond (almost get hanged the session). So please help/correct me in my query.

Regards

Jimit

Report message to a moderator

Re: Help Required In - Row Generator Query [message #622103 is a reply to message #622101]

Thu, 21 August 2014 02:17

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/95011/493455/#msg_493455

Report message to a moderator

Re: Help Required In - Row Generator Query [message #622107 is a reply to message #622103]

Thu, 21 August 2014 02:27

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Michel

I'm not getting the solution from your suggest link, actually i get the desired result untill the range of bags are smaller, as soon as it get bigger it takes too much time to execute the result. So please help me in it.

Regards

Jimit

Report message to a moderator

Re: Help Required In - Row Generator Query [message #622112 is a reply to message #622107]

Thu, 21 August 2014 03:20

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

I think following query will solve the issue. Query inspired from Michel's suggestion.

with
lines as (
   select level-1 line 
   from dual
   connect by level <= (select max(bag_end-bag_start)+1 from temp where srno = 3)
 )
select srno, srl, bag_start+line num
from temp, lines
where line <= bag_end-bag_start and srno = 3 
order by 1, 2;

Regards

Jimit

Report message to a moderator

Re: Help Required In - Row Generator Query [message #622123 is a reply to message #622112]

Thu, 21 August 2014 04:50

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

If "srl" is not required in the output, then it would be much simpler :

SQL> WITH t
  2       AS (SELECT srno,
  3                  Min(bag_start) ST,
  4                  Max(bag_end)   EN
  5           FROM   temp
  6           WHERE  srno = 2
  7           GROUP  BY srno)
  8  SELECT SRNO,
  9         st + LEVEL - 1 num
 10  FROM   t
 11  CONNECT BY LEVEL <= en - st + 1;

      SRNO        NUM
---------- ----------
         2        101
         2        102
         2        103
         2        104
         2        105
         2        106
         2        107
         2        108
         2        109
         2        110
         2        111
         2        112
         2        113
         2        114
         2        115
         2        116
         2        117
         2        118
         2        119
         2        120

20 rows selected.

Regards,
Lalit

Report message to a moderator

Re: Help Required In - Row Generator Query [message #622134 is a reply to message #622123]

Thu, 21 August 2014 05:39

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Lalitkumar,

"SRL" is required. And one more thing if the data changes in below manner then above solution may not be work perfectly.


Insert into TEMP   (SRNO, SRL, BAG_START, BAG_END) Values   (4, 1, 1001, 1010);
Insert into TEMP   (SRNO, SRL, BAG_START, BAG_END) Values   (4, 2, 1016, 1020);

WITH t
    AS (SELECT srno,
                Min(bag_start) ST,
                Max(bag_end)   EN
         FROM   temp
         WHERE  srno = 4
         GROUP  BY srno)
SELECT SRNO,
       st + LEVEL - 1 num
FROM   t
CONNECT BY LEVEL <= en - st + 1;

 SRNO        NUM
----- ----------
    4       1001
    4       1002
    4       1003
    4       1004
    4       1005
    4       1006
    4       1007
    4       1008
    4       1009
    4       1010
    4       1011
    4       1012
    4       1013
    4       1014
    4       1015
    4       1016
    4       1017
    4       1018
    4       1019
    4       1020

In above case value of "NUM" between 1011 and 1015 is not exist at all.

It is not always the case that every time following "SRL" has the serially align with previous "SRL".

Regards

Jimit

Report message to a moderator

Re: Help Required In - Row Generator Query [message #622139 is a reply to message #622134]

Thu, 21 August 2014 06:31

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Ok. Your original test case didn't have this rule, so I thought you need to generate rows starting and ending with lower and upper bound of the interval respectively for each "SRL".

Report message to a moderator

Previous Topic:	query
Next Topic:	fetching dynamic columns from a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 01:43:05 CDT 2024