| Need help to form Query to split count into multiple rows [message #662162] |
Wed, 19 April 2017 08:07  |
 |
rakesh281214
Messages: 1
Registered: April 2017
|
Junior Member |
|
|
Hi,
I have data in my table: ORDER_LABEL_TBL like below:
ORDER_NUM MODEL QUANTITY BEGIN_SEQ END_SEQ
---------- ---------- --------- --------- ---------
1242 GEH50DEEDSCB 5 50001 50005
My requirement: I want to display rows based on quantity like below:
ORDER_NUM MODEL QUANTITY BEGIN_SEQ END_SEQ
---------- ---------- --------- --------- ---------
1242 GEH50DEEDSCB 5 50001 50001
1242 GEH50DEEDSCB 5 50002 50002
1242 GEH50DEEDSCB 5 50003 50003
1242 GEH50DEEDSCB 5 50004 50004
1242 GEH50DEEDSCB 5 50005 50005
Please help me to form a query to get the above result.
Thanks,
Rakesh.
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Wed, 19 April 2017 23:47] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Need help to form Query to split count into multiple rows [message #662165 is a reply to message #662162] |
Wed, 19 April 2017 09:59  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
In addition to BlackSwan's answer:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Have a look at row generator
Especially read this post, just replace dates by simple numbers.
[Updated on: Wed, 19 April 2017 10:00] Report message to a moderator |
|
|
|
|
|
| Re: Need help to form Query to split count into multiple rows [message #662192 is a reply to message #662170] |
Thu, 20 April 2017 07:15 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara, this will only work if table has one row. And, since you are on 12C:
WITH ORDER_LABEL_TBL AS (
SELECT 1242 ORDER_NUM,'GEH50DEEDSCB' MODEL,5 QUANTITY,50001 BEGIN_SEQ,50005 END_SEQ FROM DUAL UNION ALL
SELECT 1243,'XYZ',3,1,3 FROM DUAL
)
SELECT ORDER_NUM,
MODEL,
QUANTITY,
L.BEGIN_SEQ,
L.BEGIN_SEQ END_SEQ
FROM ORDER_LABEL_TBL,
LATERAL(
SELECT BEGIN_SEQ + LEVEL - 1 BEGIN_SEQ
FROM DUAL
CONNECT BY LEVEL <= END_SEQ - BEGIN_SEQ + 1
) L
/
ORDER_NUM MODEL QUANTITY BEGIN_SEQ END_SEQ
---------- ------------ ---------- ---------- ----------
1242 GEH50DEEDSCB 5 50001 50001
1242 GEH50DEEDSCB 5 50002 50002
1242 GEH50DEEDSCB 5 50003 50003
1242 GEH50DEEDSCB 5 50004 50004
1242 GEH50DEEDSCB 5 50005 50005
1243 XYZ 3 1 1
1243 XYZ 3 2 2
1243 XYZ 3 3 3
8 rows selected.
SQL>
SY.
|
|
|
|
| Re: Need help to form Query to split count into multiple rows [message #662208 is a reply to message #662192] |
Thu, 20 April 2017 14:30 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Nice! I hadn't seen LATERAL before, so I had to look it up in the documentation. It looks like it can take the place of the old:
TABLE(CAST(MULTISET(SELECT ...) AS ...))
SCOTT@orcl_12.1.0.2.0> SELECT order_num, model, quantity,
2 l.COLUMN_VALUE begin_seq,
3 l.COLUMN_VALUE end_seq
4 FROM order_label_tbl,
5 TABLE
6 (CAST
7 (MULTISET
8 (SELECT begin_seq + LEVEL - 1 begin_seq
9 FROM DUAL
10 CONNECT BY LEVEL <= end_seq - begin_seq + 1)
11 AS SYS.ODCINUMBERLIST)) l
12 /
ORDER_NUM MODEL QUANTITY BEGIN_SEQ END_SEQ
---------- ------------ ---------- ---------- ----------
1242 GEH50DEEDSCB 5 50001 50001
1242 GEH50DEEDSCB 5 50002 50002
1242 GEH50DEEDSCB 5 50003 50003
1242 GEH50DEEDSCB 5 50004 50004
1242 GEH50DEEDSCB 5 50005 50005
1243 XYZ 3 1 1
1243 XYZ 3 2 2
1243 XYZ 3 3 3
8 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
