Home » SQL & PL/SQL » SQL & PL/SQL » Create multiple records based on condition (Oracle 11.2.0.2.0, Windows 2K)
Create multiple records based on condition [message #568216] Wed, 10 October 2012 05:00 Go to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi,
Could you please help me for the following requirement.


CREATE TABLE test1 
 (strt_num   NUMBER , 
  end_num    NUMBER , 
  des        VARCHAR2(5),
  CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
 );

INSERT INTO test1 VALUES (5,   8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');


SELECT * FROM test1

      STRT_NUM       END_NUM       DES
-------------- ------------ -------------
             5            8         GC
            10           25         AB
            12           35         PC
            22           65         LJ


The requirement is the records should be split based on below conditions
1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM

This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.

Expected Output.
       STRT_NUM         END_NUM     DES
   -------------        --------    ---------
             5            8         GC	 -- No chage, END_NUM - STRT_NUM < 10

            10           20         AB		
            21           25         AB

            12           22         PC	
            23           32         PC
            33           35         PC

            22           32         LJ  -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
            33           42         LJ  -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10  
            43           52         LJ  -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
            53           62         LJ  -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
            63           65         LJ  -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM

Re: Create multiple records based on condition [message #568220 is a reply to message #568216] Wed, 10 October 2012 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:31036568157622#5483544100346551099

Regards
Michel
Re: Create multiple records based on condition [message #568254 is a reply to message #568220] Wed, 10 October 2012 06:37 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Thank you Michel.
Re: Create multiple records based on condition [message #568259 is a reply to message #568254] Wed, 10 October 2012 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is appreciated and fair you post the final result you found.

Regards
Michel
Re: Create multiple records based on condition [message #568270 is a reply to message #568216] Wed, 10 October 2012 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I advice you to read the link I posted.

(And it is useless to put 10 empty lines at the end of your post.)

Regards
Michel
Re: Create multiple records based on condition [message #568289 is a reply to message #568270] Wed, 10 October 2012 09:20 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi Michel,

Below is the query. I'm calculating the values with respect to STRT_NUM column.
Please do let me know if it can be written in a much simpler way.

    SELECT DECODE(column_value, 0,
                 strt_num,
                 (strt_num + (column_value * 10 )+1)
	         ) strt_num ,
    CASE WHEN (strt_num +  ((column_value  + 1) * 10)) < end_num
         THEN (strt_num +  ((column_value  + 1) * 10))
    ELSE
         end_num
    END end_num,
    des
    FROM test1,
         TABLE(cast(MULTISET(SELECT LEVEL-1 FROM dual
         CONNECT BY LEVEL < ((end_num - strt_num)/10) + 1)
         AS sys.odcinumberlist))
    /

  STRT_NUM    END_NUM DES
---------- ---------- -----
         5          8 GC
        10         20 AB
        21         25 AB
        12         22 PC
        23         32 PC
        33         35 PC
        22         32 LJ
        33         42 LJ
        43         52 LJ
        53         62 LJ
        63         65 LJ

11 rows selected.


Thank you.
Re: Create multiple records based on condition [message #568303 is a reply to message #568289] Wed, 10 October 2012 11:08 Go to previous message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, this will be a good example for futur readers with the same kind of problem.

Regards
Michel
Previous Topic: Pivot
Next Topic: SQL Performance Analyzer Recommendation
Goto Forum:
  


Current Time: Mon Oct 20 19:04:23 CDT 2014

Total time taken to generate the page: 0.07503 seconds