Home » SQL & PL/SQL » SQL & PL/SQL » extract from "From to range" (Oracle 9i,9.2.0.1.0.RHEL 2.1 AS)
extract from "From to range" [message #398728] Fri, 17 April 2009 04:57 Go to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear All

I have table data like this,
SQL> create table temp ( id int primary key, stbag int, endbag int );

Table created.

SQL> insert into temp values(1,101,110);

1 row created.

SQL> insert into temp values(2,1527,1529);

1 row created.

SQL>  select id,stbag,endbag from temp;

        ID      STBAG     ENDBAG
---------- ---------- ----------
         1        101        110
         2       1527       1529


i want result like this

Id bag
1 101
1 102
1 103
1 104
1 105
1 106
1 107
1 108
1 109
1 110

2 1527
2 1528
2 1529

is it possible through select statement.

Regards

Jimit
Re: extract from "From to range" [message #398738 is a reply to message #398728] Fri, 17 April 2009 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem, search for "row generator".

Regards
Michel
Re: extract from "From to range" [message #399066 is a reply to message #398728] Mon, 20 April 2009 07:22 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel,

I have tried this

create or replace type row_table as table of number;

Create or replace function gen_row (stbag in number,num_rows in number) 
return row_table 
parallel_enable pipelined is 
begin 
  for x in stbag..num_rows loop
    pipe row (x);
  end loop;
  return;
end; 
/ 

select * from table(gen_row((select stbag from temp where id = 1),(select (endbag-stbag)+1 from temp where id = 1)));



but it is not working, so please suggest some query how do i get the result.

Regards

Jimit
Re: extract from "From to range" [message #399070 is a reply to message #398728] Mon, 20 April 2009 07:36 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

This is also not supported
SQL> SELECT * FROM ( SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= (select (endbag-stbag)+1 from temp where id = 1))
ORA-01473: cannot have subqueries in CONNECT BY clause


is there any other way to query this in 9i.

Regards

Jimit
Re: extract from "From to range" [message #399072 is a reply to message #399070] Mon, 20 April 2009 07:48 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I currently do not have access to Oracle to test below. But here is one way I can think of doing this.


create or replace type myobj as object (x int, y int)
/

create or replace type mytable as table of myobj
/


Create or replace function gen_row () 
return mytable pipelined is 
lv_temp int;
begin 
for each in (select id, stbag , endbag from temp order by id)
loop

  lv_temp := 0;

     for x in each.stbag..each.endbag
     loop
                pipe row (myboj(each.id,each.stbag+lv_temp));
                lv_temp := lv_temp + 1;
     end loop;	

end loop;
  return;
end; 
/

select * from table(cast(gen_row() as mytable))
/




[updated] small typo

[Updated on: Mon, 20 April 2009 07:57]

Report message to a moderator

Re: extract from "From to range" [message #399073 is a reply to message #399066] Mon, 20 April 2009 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Solution 1. Is not working is not an Oracle message.
Copy and paste your SQL*Plus session.
Use SQL Formatter. Your query seems much too complicated.

Solution 2. Search for row generator, there is a whole topic on this here, plus a FAQ/Wiki page... they describe solutions for each Oracle version.

Regards
Michel

[Updated on: Mon, 20 April 2009 07:58]

Report message to a moderator

Re: extract from "From to range" [message #399164 is a reply to message #398728] Tue, 21 April 2009 00:07 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear bonker,Michel

your suggested function not worked.it is giving me the following error.please reply.
SQL> ed
Wrote file afiedt.buf

  1  Create or replace function gen_row()
  2  return mytable pipelined is
  3  lv_temp int;
  4  begin
  5  for each in (select id, stbag , endbag from temp order by id)
  6  loop
  7    lv_temp := 0;
  8       for x in each.stbag..each.endbag
  9       loop
 10                  pipe row (myboj(each.id,each.stbag+lv_temp));
 11                  lv_temp := lv_temp + 1;
 12       end loop;
 13  end loop;
 14    return;
 15* end;
 16  /

Warning: Function created with compilation errors.

SQL> show err;
Errors for FUNCTION GEN_ROW:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/18     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         <an identifier> <a double-quoted delimited-identifier>
         current


whatever i have posted,was cut-pasted from sql session.

Regards

Jimit
Re: extract from "From to range" [message #399173 is a reply to message #399164] Tue, 21 April 2009 00:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You know you could try to debug it yourself instead of throwing it back into the forum
Oracle already told you that the error is on line 1.
Check the docs for the syntax of CREATE PROCEDURE/FUNCTION. Contrary to some other languages, function definitions without params should not get empty parentheses.
Re: extract from "From to range" [message #399174 is a reply to message #399164] Tue, 21 April 2009 00:35 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
It clearly says error is at line 1, you could have just removed the empty parenthesis and checked whether it is compiling .
Re: extract from "From to range" [message #399188 is a reply to message #398728] Tue, 21 April 2009 01:19 Go to previous message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Sorry friends it was my mistake,i just hurried to post reply without checking syntax or function documentations.Although with that minor change it is working successfully.
Thanks a lot.

Regards

Jimit

Previous Topic: index in date filed
Next Topic: Statistics details
Goto Forum:
  


Current Time: Sat Dec 10 12:30:30 CST 2016

Total time taken to generate the page: 0.06476 seconds