Home » SQL & PL/SQL » SQL & PL/SQL » Row generator  () 1 Vote
Row generator [message #291171] Thu, 03 January 2008 04:37 Go to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The purpose of this topic is to wrap-up all SQL or PL/SQL techniques to generate rows.
New ways are added with new Oracle versions, so read the whole topic.

Enjoy!

Regards
Michel

[Updated on: Fri, 31 August 2018 08:03]

Report message to a moderator

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: 68617
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: 7065
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: 68617
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: 68617
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: 3728
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: 68617
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: 68617
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: 68617
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: 68617
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: 68617
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: 68617
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: 146
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: 144
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: 68617
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: 68617
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: 68617
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: 34
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: 34
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: 371
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: 68617
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: 371
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: 68617
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: 371
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: 68617
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 messageGo to next message
xtender
Messages: 34
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)
Re: Puzzle n°00 - Row generator * [message #633264 is a reply to message #493455] Fri, 13 February 2015 03:40 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 08 February 2011 13:48
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.


Another way,

SQL> -- Setup
SQL> DROP TABLE t PURGE;

Table dropped.

SQL> CREATE TABLE t
  2    (id INTEGER, num INTEGER
  3    );

Table created.

SQL> INSERT INTO t
  2  SELECT level,
  3    ROUND(dbms_random.value(0,5))
  4  FROM dual
  5    CONNECT BY level <= 3
  6  /

3 rows created.

SQL> COMMIT;

Commit complete.

SQL>



SQL> -- Table content
SQL> SELECT * FROM t;

        ID        NUM
---------- ----------
         1          3
         2          2
         3          1

SQL>



SQL> SELECT id,
  2    num,
  3    level cur
  4  FROM t
  5    CONNECT BY LEVEL          <= num
  6  AND PRIOR ID                 = ID
  7  AND PRIOR dbms_random.VALUE IS NOT NULL
  8  ORDER BY 1,3
  9  /

        ID        NUM        CUR
---------- ---------- ----------
         1          3          1
         1          3          2
         1          3          3
         2          2          1
         2          2          2
         3          1          1

6 rows selected.

SQL>



Regards,
Lalit
Re: Puzzle n°00 - Row generator * [message #633284 is a reply to message #633264] Fri, 13 February 2015 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't like this method.
Using "random" procedure where there is nothing related to randomness in the data or problem and calling a procedure that has nothing to do with the problem is like to use side effect in programs (and it is).
It just seems to indicate something is not complete in the problem description (but it is complete).

What will you answer to a beginner who will ask you "why you use "AND PRIOR dbms_random.VALUE IS NOT NULL"? How and when "dbms_random.VALUE" can be null? Never? So why is it in the query?...".

Re: Puzzle n°00 - Row generator * [message #640114 is a reply to message #633284] Wed, 22 July 2015 09:26 Go to previous messageGo to next message
laurentschneider
Messages: 6
Registered: March 2007
Location: Switzerland
Junior Member
12.1.0.2

SELECT *
FROM JSON_TABLE(
  '['||REPLACE(LPAD(1,6-1,1),1,'1,')||1||']',
  '$[*]'
  COLUMNS(
    n for ordinality, 
    x NUMBER PATH'$'));


         N          X
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
Re: Puzzle n°00 - Row generator * [message #640116 is a reply to message #640114] Wed, 22 July 2015 09:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Could you please elaborate? How does the table JSON_TABLE look like? I mean, how about posting a real test case?
Re: Puzzle n°00 - Row generator * [message #640117 is a reply to message #640116] Wed, 22 July 2015 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What about the documentation Lalit? Wink
JSON_TABLE function is like XMLTABLE one but for JSON format instead of XML one.
Is this not (for those who have 12c) a real test case for a row generator as those above?

[Updated on: Mon, 06 March 2023 00:59]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #640118 is a reply to message #640117] Wed, 22 July 2015 10:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Wed, 22 July 2015 20:32

Is this not (for those who have 12c) a real test case for a row generator as those above?


No, not at all Michel. Not necessarily everybody knows about JSON_TABLE.

I know who Laurent Schneider is, ever since the day I came to know about Oracle ACE program Smile I requested him to elaborate on the solution, because it would help the community( at least who are unaware of JSON_TABLE).

EDIT : Added a link to Laurent Schneider's OraFAQ blog (just in case if someone wants to explore more).

[Updated on: Wed, 22 July 2015 10:38]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #640121 is a reply to message #640118] Wed, 22 July 2015 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

[Updated on: Wed, 06 December 2017 16:45]

Report message to a moderator

Re: Puzzle n°00 - Row generator * [message #640124 is a reply to message #640121] Wed, 22 July 2015 11:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Wed, 22 July 2015 21:34

Note you didn't explain your own "dbms_random.VALUE IS NOT NULL". Wink


Yes, because there is no need for me to say anything more, as you have already explained it beautifully Michel Smile Thanks.
Re: Puzzle n°00 - Row generator * [message #640204 is a reply to message #640114] Thu, 23 July 2015 10:01 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@Laurent,

I must say that I am not aware of "JSON_TABLE" and that's the reason I requested for more details. Perhaps, I was rude, for which I apologize. I would be happy to see your reply Smile
Re: Puzzle n°00 - Row generator * [message #640205 is a reply to message #640124] Thu, 23 July 2015 10:02 Go to previous messageGo to next message
laurentschneider
Messages: 6
Registered: March 2007
Location: Switzerland
Junior Member
@Lalit

JSON is a format to store data in a text format, something between CSV (too simple) and XML (not nice to read)

a simple JSON document could be something like
{"x":1, "y":2}


and you could extract some path with a json syntax.
$ is the document
$.x is the top element x of that document
SELECT *
FROM JSON_TABLE(
  '{"x":1, "y":2}', 
  '$' 
  COLUMNS(x NUMBER PATH '$.x')
);
X
-
1


A jason array is using carrets, [{"x":1}, {"x":2}], so there you could using * to retrieved multiple array elements as separate rows

SELECT*
FROM JSON_TABLE(
  '[{"x":1}, {"x":2}]', 
  '$[*]' 
  COLUMNS(x NUMBER PATH'$.x'));
X
-
1
2


actually it is not mandatory to name your arrays elements. Instead of [{"x":1}, {"x":2}] you could use [1,2]
SELECT*
FROM JSON_TABLE('[1,2]', '$[*]' 
  COLUMNS(x NUMBER PATH'$'));
X
-
1
2


Then I am using lpad to generate a long string

SELECT LPAD(1,5,1) STR FROM DUAL;
STR
-----
11111

Left-padding one with 5 -or 500- one's.

Then replace 1 by 1,
SELECT REPLACE(LPAD(1,5,1),1,'1,') STR FROM DUAL;
STR
----------
1,1,1,1,1,


and I almost have my json table [1,1,1,1,1,1]

The true generator is lpad. The rest is a personal creation Smile
Re: Puzzle n°00 - Row generator * [message #640206 is a reply to message #640205] Thu, 23 July 2015 10:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thank you so much Laurent Smile
Re: Puzzle n°00 - Row generator * [message #640207 is a reply to message #640205] Thu, 23 July 2015 10:08 Go to previous messageGo to previous message
laurentschneider
Messages: 6
Registered: March 2007
Location: Switzerland
Junior Member
To get rid of x, you could use n*x
SELECT n*x FROM JSON_TABLE('['||REPLACE(LPAD(1,5,1),1,'1,')||1||']','$[*]'COLUMNS(n for ordinality, x NUMBER PATH'$'));
       N*X
----------
         1
         2
         3
         4
         5
         6


But n without x doesn't work. Probably a bug
SELECT n FROM JSON_TABLE('['||REPLACE(LPAD(1,5,1),1,'1,')||1||']','$[*]'COLUMNS(n for ordinality, x NUMBER PATH'$'))
              *
Error at line 1
ORA-40497: only one ordinality column permitted
Previous Topic: Do users ever create tables in these schemas?
Next Topic: How to use [code] tags and make your code easier to read
Goto Forum:
  


Current Time: Mon Mar 18 23:10:18 CDT 2024