Home » SQL & PL/SQL » SQL & PL/SQL » problem in row generation (Oracle 9i, 9.2.0.1.0., RHEL 2.1 AS)
problem in row generation [message #409614] Tue, 23 June 2009 05:38 Go to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

here i'm describing my test case.i have a problem in result set but i'm not able to find , so please help me out.
create table temp (id number,pid number,idsr number,stbag number,endbag number);

insert into temp values(1,1,1,1,5);
insert into temp values(1,1,2,11,15);
insert into temp values(1,2,3,21,25);

commit;

select pid,idsr,bag
from (select (stbag+num)-1 bag
   ,pid
   ,idsr
      from  (select level num
             from  (select stbag,endbag from  temp where id = 1 and pid = 2) gen
             connect by level <= (gen.endbag-gen.stbag)+1) src
            ,temp
      where  src.num <= endbag and id = 1 and pid = 2)
order by pid,idsr,bag



       PID       IDSR        BAG
---------- ---------- ----------
         2          3         21
         2          3         22
         2          3         23
         2          3         24
         2          3         25



here actually i get the result perfectly ,but it restricted to pid. i want to remove pid from my query and i want same result for all pid within same id.result set look like this.can anyone suggest what to do for below result.

PID IDSR BAG
---------- ---------- ----------
1 1 1
1 1 2
1 1 3
1 1 4
1 1 5


1 2 11
1 2 12
1 2 13
1 2 14
1 2 15

2 3 21
2 3 22
2 3 23
2 3 24
2 3 25

Regards

Jimit
Re: problem in row generation [message #409619 is a reply to message #409614] Tue, 23 June 2009 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the restriction on pid, and join the inner sources, for instance.
Or use a generic generator and limit the number of lines for each pid.

Regards
Michel

[Updated on: Tue, 23 June 2009 06:01]

Report message to a moderator

Re: problem in row generation [message #409625 is a reply to message #409614] Tue, 23 June 2009 06:19 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

i have removed restriction on pid and try this but still i'm not able to get it right.Actually i'm not getting 100% what u suggest.i have tried this. can u suggest where i have get proper join. please tell me something about generic generator.

select pid,idsr,bag
from (select 
	  pid	
	  ,idsr
	  ,row_number() over (order by pid,idsr) bag
      from  (select level num,totbag
             from  (select sum((endbag-stbag)+1) totbag from  temp where id = 1) gen
             connect by level <= totbag) src
            ,temp
      where  src.num <= totbag and id = 1)
order by pid,idsr,bag



Regards

Jimit
Re: problem in row generation [message #409629 is a reply to message #409625] Tue, 23 June 2009 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have removed restriction on pid

but did not
Quote:
and join the inner sources


Second solution, you already use a row generator do not limit it by endbag-stbag, put this limit outside it when you will join with temp.

Regards
Michel
Re: problem in row generation [message #409630 is a reply to message #409614] Tue, 23 June 2009 06:30 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

i get the solution as you suggested.thanks for suggestions.

select pid,idsr,bag
from (select 
	  pid	
	  ,idsr
	  ,num bag
      from  (select level num
             from  (select max(endbag) endbag from  temp where id = 1) gen
             connect by level <= endbag) src
            ,temp
      where  src.num between stbag and endbag and id = 1)
order by pid,idsr,bag



Regards

Jimit
Re: problem in row generation [message #409632 is a reply to message #409630] Tue, 23 June 2009 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Antoher way to write it:
SQL> with 
  2    lines as ( 
  3      select level-1 line from dual
  4      connect by level <= (select max(endbag-stbag+1) from temp)
  5    )
  6  select pid, idsr, stbag+line bag
  7  from temp, lines
  8  where line <= endbag-stbag
  9  order by 1, 2, 3
 10  /
       PID       IDSR        BAG
---------- ---------- ----------
         1          1          1
         1          1          2
         1          1          3
         1          1          4
         1          1          5

         1          2         11
         1          2         12
         1          2         13
         1          2         14
         1          2         15

         2          3         21
         2          3         22
         2          3         23
         2          3         24
         2          3         25

Regards
Michel
Re: problem in row generation [message #409793 is a reply to message #409614] Wed, 24 June 2009 01:43 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

i have run your solution and got the following error. I think problem occurred because of my database version. can you suggest me some information about generic generator.
 with 
   lines as ( 
     select level-1 line from dual
     connect by level <= (select max(endbag-stbag+1) from temp)
   )
 select pid, idsr, stbag+line bag
 from temp, lines
 where line <= endbag-stbag
 order by 1, 2, 3;
connect by level <= (select max(endbag-stbag+1) from temp)
/

ERROR at line 4:
ORA-01473: cannot have subqueries in CONNECT BY clause


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Linux: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


Regards

Jimit
Re: problem in row generation [message #409798 is a reply to message #409793] Wed, 24 June 2009 02:19 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rewrite it in this way, it should work:
SQL> with 
  2    maxval as ( select max(endbag-stbag+1) maxval from temp where rownum >= 1 ),
  3    lines as ( 
  4      select level-1 line from maxval
  5      connect by level <= maxval
  6    )
  7  select pid, idsr, stbag+line bag
  8  from temp, lines
  9  where line <= endbag-stbag
 10  order by 1, 2, 3
 11  /
       PID       IDSR        BAG
---------- ---------- ----------
         1          1          1
         1          1          2
         1          1          3
         1          1          4
         1          1          5

         1          2         11
         1          2         12
         1          2         13
         1          2         14
         1          2         15

         2          3         21
         2          3         22
         2          3         23
         2          3         24
         2          3         25

Regards
Michel
Previous Topic: Difference between two table structure
Next Topic: Sql Tuning
Goto Forum:
  


Current Time: Sun Dec 11 04:04:50 CST 2016

Total time taken to generate the page: 0.04170 seconds