Home » Other » General » Puzzle n°00 - Row generator *
Puzzle n°00 - Row generator * [message #291171] Thu, 03 January 2008 04:37 Go to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The purpose of this puzzle is to wrap-up all SQL or PL/SQL techniques to generate rows.

Enjoy!

Regards
Michel
Re: Puzzle n°00 - Row generator * [message #291177 is a reply to message #291171] Thu, 03 January 2008 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before 8i
SQL> DEFINE N=3
SQL> SELECT ROWNUM 
  2  FROM ALL_OBJECTS
  3  WHERE ROWNUM <= &N
  4  /
    ROWNUM
----------
         1
         2
         3

SQL 8i
SQL> SELECT ROWNUM 
  2  FROM ( SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3,4,5,6,7,8,9,10) )
  3  WHERE ROWNUM <= &N
  4  /
    ROWNUM
----------
         1
         2
         3

SQL 9i
SQL> SELECT * 
  2  FROM ( SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N )
  3  /
    ROWNUM
----------
         1
         2
         3

SQL 10g
SQL> SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N;
    ROWNUM
----------
         1
         2
         3

Using a pipeline function (9i and up)
SQL> create or replace type row_table as table of number;
  2  /

Type created.

SQL> Create or replace function gen_row (num_rows in number) 
  2  return row_table 
  3  parallel_enable pipelined is 
  4  begin 
  5    for x in 1..num_rows loop
  6      pipe row (x);
  7    end loop;
  8    return;
  9  end; 
 10  / 

Function created.

SQL> select * from table(gen_row(&N)); 
COLUMN_VALUE
------------
           1
           2
           3

3 rows selected.

I know there are other ways but couldn't remember now, especially there is a way using XML query, if someone can post it.

Regards
Michel

[Updated on: Fri, 21 October 2011 01:55]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #291190 is a reply to message #291171] Thu, 03 January 2008 05:43 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
This is on 10g.
SQL> define n=5
SQL> select to_number(column_value) colval
  2  from xmltable('for $i in 1 to &n return $i');

    COLVAL
----------
         1
         2
         3
         4
         5
Re: Puzzle n°00 - Row generator * [message #291199 is a reply to message #291171] Thu, 03 January 2008 06:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This is also in 10g:

SQL> select integer_value
  2   from   dual
  3  where  1=2
  4  model
  5  dimension by ( 0 as key )
  6  measures     ( 0 as integer_value )
  7  rules upsert ( integer_value[ for key from 1 to 5 increment 1 ] = cv(key) );

INTEGER_VALUE
-------------
            1
            2
            3
            4
            5

Rajuvan.

[Edit MC: change 10 to 5 to remove superfluous lines, please use a variable to show us what part is dependent on the parameter]

[Updated on: Sat, 12 February 2011 08:54] by Moderator

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #291216 is a reply to message #291171] Thu, 03 January 2008 07:19 Go to previous messageGo to next message
Maaher
Messages: 7045
Registered: December 2001
Senior Member
Perhaps a link to the Wiki page is in its place here?

Oracle Row Generator Techniques

I encourage you all to add missing techniques to that page too.

MHE
Re: Puzzle n°00 - Row generator * [message #291224 is a reply to message #291171] Thu, 03 January 2008 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another MODEL query (>= 10g)

SQL> select val
  2  from (select 1 rn from dual)
  3  model
  4    dimension by (rn)
  5    measures (1 val)
  6  rules 
  7    iterate (&N)
  8    ( val[iteration_number] = iteration_number+1 )
  9  order by val
 10  /
       VAL
----------
         1
         2
         3
         4
         5

Regards
Michel

[Updated on: Sat, 12 February 2011 08:55]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #456827 is a reply to message #291171] Wed, 19 May 2010 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another 10g and up way:
SQL> select * 
  2  from table (cast (multiset (select level from dual connect by level <= 5) 
  3                    as sys.OdciNumberList ) 
  4             )
  5  /
COLUMN_VALUE
------------
           1
           2
           3
           4
           5

Regards
Michel

[Updated on: Sat, 12 February 2011 08:57]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #457173 is a reply to message #456827] Fri, 21 May 2010 08:30 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Um.... am I missing something? Or does that one just stuff a much simpler row generator into a collection and then drag it out again? Seems unnecessary.
Re: Puzzle n°00 - Row generator * [message #457174 is a reply to message #457173] Fri, 21 May 2010 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do not miss something but as someone mentioned it in a topic, I added also here.
Actually, I find useful to add it because it shows an example of usage of collection.

Regards
Michel
Re: Puzzle n°00 - Row generator * [message #462302 is a reply to message #291171] Wed, 23 June 2010 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A new in 11g using the new recursive query:
SQL> WITH data(r) AS (
  2    SELECT 1 r FROM dual
  3    UNION ALL
  4    SELECT r+1 FROM data WHERE r < 5
  5    )
  6  SELECT r FROM data
  7  /
         R
----------
         1
         2
         3
         4
         5

Regards
Michel
Re: Puzzle n°00 - Row generator * [message #463394 is a reply to message #291171] Thu, 01 July 2010 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From the row generator ways, here a calendar/date generator:
SQL> def date_start = '13/11/2010'
SQL> def date_end   = '22/11/2010'
SQL> with 
  2    data as (
  3      select to_date('&date_start', 'DD/MM/YYYY') date1,
  4             to_date('&date_end',   'DD/MM/YYYY') date2
  5      from dual
  6    )
  7  select to_char(date1+level-1, 'DD/MM/YYYY') the_date
  8  from data
  9  connect by level <= date2-date1+1
 10  /
THE_DATE
----------
13/11/2010
14/11/2010
15/11/2010
16/11/2010
17/11/2010
18/11/2010
19/11/2010
20/11/2010
21/11/2010
22/11/2010


To get all dates of the current year:
select trunc(sysdate,'YEAR') + level - 1 the_date
from dual
connect by level <= trunc(add_months(sysdate,12),'YEAR') - trunc(sysdate,'YEAR')
/


To get all dates of the current month:
select trunc(sysdate,'MONTH') + level - 1 the_date
from dual
connect by level <= last_day(sysdate) - trunc(sysdate,'MONTH') + 1
/


To get all dates of the current ISO week (starts a Monday and ends a Sunday):
select trunc(sysdate,'IW') + level - 1 the_date
from dual
connect by level <= 7
/


To get the previous 6 months from the current date:
select trunc(add_months(sysdate, -1*level), 'month') month 
from dual 
connect by level <= 6
/


Regards
Michel

[Updated on: Wed, 05 January 2011 04:42]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #483276 is a reply to message #463394] Fri, 19 November 2010 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Example of using row generator.

When you have several rows in a table with a start and end dates, the following query can be used:
-- Setup
create table t (id integer, start_date date, end_date date);
insert into t 
select level, sysdate-dbms_random.value(0,5), sysdate+dbms_random.value(0,5)
from dual
connect by level <= 3
/
commit;

SQL> -- Table content
SQL> select * from t;
        ID START_DATE  END_DATE
---------- ----------- -----------
         1 18-NOV-2010 21-NOV-2010
         2 16-NOV-2010 23-NOV-2010
         3 17-NOV-2010 20-NOV-2010

3 rows selected.

SQL> -- Expanded
SQL> break on id dup skip 1
SQL> select id, column_value cur_date
  2  from t, 
  3       table (cast (multiset ( select start_date+level-1 
  4                               from dual connect by level <= end_date-start_date+1
  5                             )
  6                    as sys.odciDateList
  7                   )
  8             )
  9  /
        ID CUR_DATE
---------- -----------
         1 18-NOV-2010
         1 19-NOV-2010
         1 20-NOV-2010
         1 21-NOV-2010

         2 16-NOV-2010
         2 17-NOV-2010
         2 18-NOV-2010
         2 19-NOV-2010
         2 20-NOV-2010
         2 21-NOV-2010
         2 22-NOV-2010
         2 23-NOV-2010

         3 17-NOV-2010
         3 18-NOV-2010
         3 19-NOV-2010
         3 20-NOV-2010

Thanks to Barbara Boehmer and Tom Kyte to learn me this new solution over the previous one which was:
SQL> with
  2    lines as (
  3      select level-1 line 
  4      from dual
  5      connect by level <= (select max(ceil(end_date-start_date)) from t)
  6    )
  7  select id, start_date+line cur_date
  8  from t, lines
  9  where line <= end_date-start_date
 10  order by 1, 2
 11  /
        ID CUR_DATE
---------- -----------
         1 18-NOV-2010
         1 19-NOV-2010
         1 20-NOV-2010
         1 21-NOV-2010

         2 16-NOV-2010
         2 17-NOV-2010
         2 18-NOV-2010
         2 19-NOV-2010
         2 20-NOV-2010
         2 21-NOV-2010
         2 22-NOV-2010
         2 23-NOV-2010

         3 17-NOV-2010
         3 18-NOV-2010
         3 19-NOV-2010
         3 20-NOV-2010


Regards
Michel

[Updated on: Tue, 08 February 2011 02:06]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #493455 is a reply to message #483276] Tue, 08 February 2011 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another similar example that was asked several times in forum.

You have a table with an id and a number, duplicate each id in as many rows as the number shows.

-- Setup
drop table t purge;
create table t (id integer, num integer);
insert into t 
select level, round(dbms_random.value(0,5))
from dual
connect by level <= 3
/
commit;


SQL> -- Table content
SQL> select * from t;
        ID        NUM
---------- ----------
         1          2
         2          4
         3          3


SQL> -- Expanded
SQL> break on id dup skip 1
SQL> -- Old way
SQL> with 
  2    lines as (
  3      select level cur 
  4      from dual
  5      connect by level <= ( select max(num) from t )
  6    )
  7  select id, num, cur
  8  from t, lines
  9  where cur <= num
 10  order by 1, 3
 11  /
        ID        NUM        CUR
---------- ---------- ----------
         1          2          1
         1          2          2

         2          4          1
         2          4          2
         2          4          3
         2          4          4

         3          3          1
         3          3          2
         3          3          3


SQL> -- New way
SQL> select id, num, column_value cur
  2  from t,
  3       table (cast (multiset (select level from dual connect by level <= num)
  4                    as sys.odciNumberList
  5                   )
  6             )
  7  order by 1, 3
  8  /
        ID        NUM        CUR
---------- ---------- ----------
         1          2          1
         1          2          2

         2          4          1
         2          4          2
         2          4          3
         2          4          4

         3          3          1
         3          3          2
         3          3          3


Regards
Michel
Re: Puzzle n°00 - Row generator * [message #493456 is a reply to message #493455] Tue, 08 February 2011 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another classical example of using row generator is the "columns to rows" transformation.

We have a table with an id and 6 columns which are in fact 3 couples of columns (you can imagine they are zip code + text address for 3 possible addresses of the id). Now you want for each id, 3 rows with one couple of columns (one zipcode + address).

-- Setup
drop table t purge;
create table t (
  id    integer,
  col11 integer,
  col12 varchar2(5),
  col21 integer,
  col22 varchar2(5),
  col31 integer,
  col32 varchar2(5)
)
/
insert all 
into t values (1, 11, 'AAAAA', 12, 'BBBBB', 13, 'CCCCC')
into t values (2, 21, 'DDDDD', 22, 'EEEEE', 23, 'FFFFF')
select null from dual
/
commit;


SQL> -- Table content
SQL> select * from t order by id;
        ID      COL11 COL12      COL21 COL22      COL31 COL32
---------- ---------- ----- ---------- ----- ---------- -----
         1         11 AAAAA         12 BBBBB         13 CCCCC
         2         21 DDDDD         22 EEEEE         23 FFFFF


SQL> -- Columns to rows conversion
SQL> break on id dup skip 1
SQL> with 
  2    lines as (select level line from dual connect by level <= 3) -- row generator
  3  select id, line, 
  4         decode(line, 1, col11, 2, col21, 3, col31) col1,
  5         decode(line, 1, col12, 2, col22, 3, col32) col2
  6  from t, lines
  7  order by id, line
  8  /
        ID       LINE       COL1 COL2
---------- ---------- ---------- -----
         1          1         11 AAAAA
         1          2         12 BBBBB
         1          3         13 CCCCC

         2          1         21 DDDDD
         2          2         22 EEEEE
         2          3         23 FFFFF


Regards
Michel
Re: Puzzle n°00 - Row generator * [message #517716 is a reply to message #493456] Wed, 27 July 2011 11:06 Go to previous messageGo to next message
anil_mk
Messages: 110
Registered: August 2006
Location: Bangalore, India
Senior Member

Thanks Michel for sharing with us.

Really good examples.

Thanks,
Anil MK
Re: Puzzle n°00 - Row generator * [message #527949 is a reply to message #517716] Thu, 20 October 2011 23:59 Go to previous messageGo to next message
oralover2006
Messages: 118
Registered: January 2010
Location: India
Senior Member
anil_mk wrote on Wed, 27 July 2011 21:36
Thanks Michel for sharing with us.

Really good examples.

Thanks,
Anil MK


Yes, its treasure for me as i am in learning stage ( off course doing job, but verious areas are still in learning phase ). When i am getting problem, i am coming here to get help from these Experts, and always find them much helpful Smile

as i tried below, its generating wrong result for ID 1 and 3, for both End Date 26-Oct-11 and 25-oct-11 is missing, but ID 2 is correct all records are showing from Start to End. I just copy & paste from Michel post of "Fri 19 November 2010 22:41", is there anything i missed or something else? please help.


devtest@ Test.DB> create table t (id integer, start_date date, end_date date);

Table created.

devtest@ Test.DB> insert into t 
  2  select level, sysdate-dbms_random.value(0,5), sysdate+dbms_random.value(0,5)
  3  from dual
  4  connect by level <= 3
  5  /

3 rows created.

devtest@ Test.DB> commit;

Commit complete.
devtest@ Test.DB> select * from t order by 1;

       ID START_DAT END_DATE
--------- --------- ---------
        1 20-OCT-11 26-OCT-11
        2 16-OCT-11 24-OCT-11
        3 19-OCT-11 25-OCT-11

3 rows selected.

devtest@ Test.DB> with
  2     lines as (
  3               select level-1 line
  4                 from dual
  5              connect by level <= (select max(ceil(end_date-start_date)) from t)
  6              )
  7  select id, start_date+line cur_date
  8    from t, lines
  9   where line <= end_date-start_date
 10   order by 1, 2
 11  .
devtest@ Test.DB> break on id dup skip 1
devtest@ Test.DB> /

       ID CUR_DATE
--------- ---------
        1 20-OCT-11
        1 21-OCT-11
        1 22-OCT-11
        1 23-OCT-11
        1 24-OCT-11
        1 25-OCT-11

        2 16-OCT-11
        2 17-OCT-11
        2 18-OCT-11
        2 19-OCT-11
        2 20-OCT-11
        2 21-OCT-11
        2 22-OCT-11
        2 23-OCT-11
        2 24-OCT-11

        3 19-OCT-11
        3 20-OCT-11
        3 21-OCT-11
        3 22-OCT-11
        3 23-OCT-11
        3 24-OCT-11


21 rows selected.

devtest@ Test.DB> 

[Updated on: Fri, 21 October 2011 02:48]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #569066 is a reply to message #493456] Fri, 19 October 2012 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On more very often question is about splitting strings containing several values.
Here's an example of such question (http://www.orafaq.com/forum/t/184584/102589/):
Create table a ( Objectid number, Value varchar2(2000));
Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');

And here's one answer in the current versions:
SQL> break on objectid dup skip 1
SQL> col value format a10
SQL> With data as (select objectid, ','||value||',' value from a)
  2  select objectid, column_value value_nb,
  3         substr(value,
  4                instr(value, ',', 1, column_value)+1,
  5                instr(value, ',', 1, column_value+1)-instr(value, ',', 1, column_value)-1
  6               ) value
  7  from data,
  8       table(cast(multiset(select level from dual 
  9                           connect by level < length(value)-length(replace(value,',')))
 10             as sys.odciNumberList))
 11  order by 1, 2
 12  /
  OBJECTID   VALUE_NB VALUE
---------- ---------- ----------
        12          1 2
        12          2 3
        12          3 4

        13          1 8
        13          2 7
        13          3 4

        14          1 3
        14          2 8
        14          3 9

        15          1 6
        15          2 3
        15          3 11

Regards
Michel

[Updated on: Fri, 19 October 2012 06:15]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #569069 is a reply to message #569066] Fri, 19 October 2012 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And starting with 11gR2:
SQL> break on objectid dup skip 1
SQL> col value format a10
SQL> select objectid, column_value value_nb,
  2         regexp_substr(value, '[^,]+', 1, column_value) value
  3  from a,
  4       table(cast(multiset(select level from dual
  5                           connect by level <= regexp_count(value,',')+1)
  6             as sys.odciNumberList))
  7  order by 1, 2
  8  /

  OBJECTID   VALUE_NB VALUE
---------- ---------- ----------
        12          1 2
        12          2 3
        12          3 4

        13          1 8
        13          2 7
        13          3 4

        14          1 3
        14          2 8
        14          3 9

        15          1 6
        15          2 3
        15          3 11

Regards
Michel

[Updated on: Fri, 19 October 2012 06:17]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #579413 is a reply to message #569069] Tue, 12 March 2013 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And in 10g with regular expressions (this solution is less efficient than the INSTR and SUBSTR one above):
SQL> break on objectid dup skip 1
SQL> col value format a10
SQL> select objectid, column_value value_nb,
  2         regexp_substr(value, '[^,]+', 1, column_value) value
  3  from a,
  4       table(cast(multiset(select level from dual
  5                           connect by regexp_substr(value, '[^,]+', 1, level) is not null)
  6             as sys.odciNumberList))
  7  order by 1, 2
  8  /
  OBJECTID   VALUE_NB VALUE
---------- ---------- ----------
        12          1 2
        12          2 3
        12          3 4

        13          1 8
        13          2 7
        13          3 4

        14          1 3
        14          2 8
        14          3 9

        15          1 6
        15          2 3
        15          3 11

Regards
Michel
Re: Puzzle n°00 - Row generator * [message #580667 is a reply to message #579413] Tue, 26 March 2013 13:58 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

In addition, a couple other xml versions:
SQL> select * from xmltable('&1 to &2' columns i int path '.');
Enter value for 1: 10
Enter value for 2: 15

         I
----------
        10
        11
        12
        13
        14
        15

6 rows selected.

SQL> select * from xmltable('1 to &1' columns i for ordinality);
Enter value for 1: 5

         I
----------
         1
         2
         3
         4
         5
Re: Puzzle n°00 - Row generator * [message #580668 is a reply to message #580667] Tue, 26 March 2013 14:05 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Another one - with powermultiset:
SQL> l
  1  select rownum
  2  from table(powermultiset(sys.ku$_objnumset(1,2,3,4,5,6,7,8,9,10)))
  3* where rownum<=10
SQL> /

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Elapsed: 00:00:00.00

PS. sys.ku$_objnumset can be replaced with any simple collection
Re: Puzzle n°00 - Row generator * [message #582946 is a reply to message #580668] Tue, 23 April 2013 10:55 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Another situation in a hierarchy, is to get a node with its father, a node with its grand father and so on.

drop table test;

create table test
(
	father	varchar2(10)	,
	son   	varchar2(10)
)
;


insert into test values ('A', 'B');
insert into test values ('A', 'C');

insert into test values ('B', 'D');
insert into test values ('B', 'E');

insert into test values ('C', 'F');
insert into test values ('C', 'G');
insert into test values ('C', 'H');

insert into test values ('E', 'I');


so we have this hierarchy :
          A
       /     \
      B       C
    /   \   / | \
    D    E  F  G  H
         |
         I

For I we want to get these couples :
I-E, I-B, I-A.

Here is the generator :

column my_path format A10
column father format A2
column son format A2
column nb format 99

SELECT DISTINCT father, son
FROM ( SELECT substr(my_path, 
                     instr(my_path, '/', 1, 1)+1, 
                     instr(my_path, '/', 1, 2)-instr(my_path, '/', 1, 1)-1) 
                father,
              substr(my_path, 
                     instr(my_path, '/', 1, column_value)+1, 
                     instr(my_path, '/', 1, column_value+1)
                     - instr(my_path, '/', 1, column_value)-1) 
                son,
              my_path, my_level + 1 my_level
      FROM ( SELECT sys_connect_by_path(father,'/') || '/' || son || '/' my_path,
                    LEVEL my_level
             FROM test
             CONNECT BY PRIOR son = father ), 
           table(cast(multiset(SELECT LEVEL FROM dual CONNECT BY LEVEL <= my_level + 1) 
                      AS sys.odciNumberList))
      ORDER BY 2 )
WHERE father <> son
ORDER BY 1, 2
/


FA SO
-- --
A  B
A  C
A  D
A  E
A  F
A  G
A  H
A  I
B  D
B  E
B  I
C  F
C  G
C  H
E  I

15 ligne(s) sélectionnée(s).

[Updated on: Tue, 23 April 2013 13:24] by Moderator

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #582956 is a reply to message #582946] Tue, 23 April 2013 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not just:
SQL> select connect_by_root father father, son
  2  from test
  3  connect by prior son = father
  4  order by 1, 2
  5  /
FATHER     SON
---------- ----------
A          B
A          C
A          D
A          E
A          F
A          G
A          H
A          I
B          D
B          E
B          I
C          F
C          G
C          H
E          I

15 rows selected.

Regards
Michel

[Edit: added ORDER BY]

[Updated on: Tue, 23 April 2013 13:22]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #583081 is a reply to message #582956] Thu, 25 April 2013 10:06 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

My query works from 9i and up. connect_by_root appears in 10g.
Re: Puzzle n°00 - Row generator * [message #583092 is a reply to message #583081] Thu, 25 April 2013 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So,is this not:
SQL> with
  2    data as (
  3      select substr(sys_connect_by_path(father,',')||',',2) father, son
  4      from test
  5      connect by prior son = father
  6    )
  7  select substr(father, 1, instr(father,',')-1) father, son
  8  from data
  9  order by 1, 2
 10  /
FATHER               SON
-------------------- ----------
A                    B
A                    C
A                    D
A                    E
A                    F
A                    G
A                    H
A                    I
B                    D
B                    E
B                    I
C                    F
C                    G
C                    H
E                    I

Wink

Regards
Michel
Re: Puzzle n°00 - Row generator * [message #583095 is a reply to message #583092] Thu, 25 April 2013 13:13 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Smile yes it is, it really is !!! Am not Michel just Amine, so could you replace my query by yours ?
Re: Puzzle n°00 - Row generator * [message #583096 is a reply to message #583095] Thu, 25 April 2013 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, your effort to use a row generator deserves to be here as an example and anyone that is interested in the method can analyse it.

Regards
Michel

[Updated on: Thu, 25 April 2013 13:41]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #589899 is a reply to message #291171] Thu, 11 July 2013 15:49 Go to previous message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Since Oracle 12c finally allowed to use old "lateral":
with t as (
          select 5 a from dual
          union all
          select 2 from dual
)
select a,b
from t
    ,lateral(select level b from dual connect by level<=a)
Previous Topic: Oracle 10g Installed with the TNS port no 1522
Next Topic: Task of the day
Goto Forum:
  


Current Time: Tue Sep 02 15:54:19 CDT 2014

Total time taken to generate the page: 0.12874 seconds