Home » SQL & PL/SQL » SQL & PL/SQL » Row generator
( ) 1 Vote
| Row generator [message #291171] |
Thu, 03 January 2008 04:37  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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  |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #291199 is a reply to message #291171] |
Thu, 03 January 2008 06:19 |
 |
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 #291224 is a reply to message #291171] |
Thu, 03 January 2008 07:46 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #463394 is a reply to message #291171] |
Thu, 01 July 2010 02:46 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #527949 is a reply to message #517716] |
Thu, 20 October 2011 23:59 |
oralover2006
Messages: 144
Registered: January 2010
Location: India
|
Senior Member |
|
|
anil_mk wrote on Wed, 27 July 2011 21:36Thanks 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 
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
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 |
|
|
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 |
Amine
Messages: 376
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #583092 is a reply to message #583081] |
Thu, 25 April 2013 12:47 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
Regards
Michel
|
|
|
|
|
|
|
|
|
|
| Re: Puzzle n°00 - Row generator * [message #633264 is a reply to message #493455] |
Fri, 13 February 2015 03:40 |
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:48Another 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 #640118 is a reply to message #640117] |
Wed, 22 July 2015 10:19 |
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 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 #640205 is a reply to message #640124] |
Thu, 23 July 2015 10:02 |
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
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
|
|
|
|
|
|
| Re: Puzzle n°00 - Row generator * [message #640207 is a reply to message #640205] |
Thu, 23 July 2015 10:08 |
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
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 00:05:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
