Home » SQL & PL/SQL » SQL & PL/SQL » Need help to form Query to split count into multiple rows
Need help to form Query to split count into multiple rows [message #662162] Wed, 19 April 2017 08:07 Go to next message
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 #662164 is a reply to message #662162] Wed, 19 April 2017 08:31 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

https://community.oracle.com/thread/4037970
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
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 #662170 is a reply to message #662162] Wed, 19 April 2017 23:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8805
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> SELECT * FROM order_label_tbl
  2  /

 ORDER_NUM MODEL          QUANTITY  BEGIN_SEQ    END_SEQ
---------- ------------ ---------- ---------- ----------
      1242 GEH50DEEDSCB          5      50001      50005

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT  order_num, model, quantity,
  2  	     begin_seq + ROWNUM - 1 begin_seq,
  3  	     begin_seq + ROWNUM - 1 end_seq
  4  FROM    order_label_tbl
  5  CONNECT BY LEVEL <= end_seq - begin_seq + 1
  6  /

 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

5 rows selected.
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
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 Go to previous message
Barbara Boehmer
Messages: 8805
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.
Previous Topic: merge issue
Next Topic: DEFINE_COLUMN in DBMS_SQL
Goto Forum:
  


Current Time: Sat Feb 24 17:28:08 CST 2018

Total time taken to generate the page: 0.02006 seconds