1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need to insert a record between a particular record

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Jul 18, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    I have a requirement as below

    I have a table with 3 columns

    Col1 Col2 Col3 lowrange highrange
    BRID A 1 1 3
    BRID A 3 1 3
    BRID C 1 1 5
    BRID C 2 1 5
    BRID C 3 1 5
    BRID C 5 1 5
    BRID B 1 1 7
    BRID B 2 1 7
    BRID B 3 1 7
    BRID B 5 1 7
    BRID B 7 1 7

    We need to insert the missing number in col3 between lowrange and highrange.
    I have managed to insert the missing numbers.

    However, if we need to insert exactly between the missed place.

    I would not be able to use order by as Col2 is not maintained in any order.

    The output should be as below

    Col1 Col2 Col3
    BRID A 1
    BRID A 2
    BRID A 3
    BRID C 1
    BRID C 2
    BRID C 3
    BRID C 4
    BRID C 5
    BRID B 1
    BRID B 2
    BRID B 3
    BRID B 4
    BRID B 5
    BRID B 6
    BRID B 7

    Kindly let me now any logic to achieve the same
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    for example:
    Code (Text):

    WITH
    your_tab (col1,col2,col3,lr,hr) AS
    (
    SELECT 'BRID' ,'A', 1, 1, 3 FROM dual UNION ALL
    SELECT 'BRID' ,'A', 3, 1, 3 FROM dual UNION ALL
    SELECT 'BRID','C', 1, 1, 5 FROM dual UNION ALL
    SELECT 'BRID','C', 4, 1, 5 FROM dual
    ),
    step1 AS
    (
    SELECT col1,col2,max(hr-lr+1) cnt FROM your_tab GROUP BY col1,col2
    -- may be  SELECT col1,col2,hr-lr+1 cnt FROM your_tab WHERE col3 = 1

    )
    ,step2 AS (
    SELECT  z.* ,LEVEL new_val FROM step1 z

    CONNECT BY LEVEL<=cnt
    AND PRIOR sys_guid() IS NOT NULL
    AND PRIOR col1= col1
    AND PRIOR col2 = col2
    )
    SELECT
    st2.col1,st2.col2,st2.new_val,yt1.col3
    FROM step2 st2
    LEFT JOIN your_tab yt1 ON yt1.col1 =st2.col1 AND yt1.col2 = st2.col2 AND yt1.col3 = st2.new_val
    ORDER BY col2,new_val;
     
    Code (Text):

    WITH
    your_tab (col1,col2,col3,lr,hr) AS
    (
    SELECT 'BRID' ,'A', 1, 1, 3 FROM dual UNION ALL
    SELECT 'BRID' ,'A', 3, 1, 3 FROM dual UNION ALL
    SELECT 'BRID','C', 1, 1, 5 FROM dual UNION ALL
    SELECT 'BRID','C', 4, 1, 5 FROM dual
    ),
    step1 AS
    (
    SELECT col1,col2,to_number(z.column_value) FROM your_tab y,XMLTABLE
    ('xs:integer($lr) to xs:integer($hr) '  
    PASSING
    y.lr AS "lr" ,y.hr AS "hr"
    ) z
    WHERE col3 = 1
    )
    SELECT  * FROM step1;
     
     
    Last edited: Jul 18, 2017
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your requirement is in need of modification as you can't insert a record into a specific location in a heap table. You can get the order you want with the data you have provided:

    Code (SQL):
    BING @ quanghoo > CREATE TABLE testit(
      2  col1       varchar2(10),
      3  col2       varchar2(1),
      4  col3       NUMBER);

    TABLE created.

    BING @ quanghoo >
    BING @ quanghoo > INSERT ALL
      2  INTO testit
      3  VALUES('BRID','A',1)
      4  INTO testit
      5  VALUES('BRID','A',2)
      6  INTO testit
      7  VALUES('BRID','A',3)
      8  INTO testit
      9  VALUES('BRID','C',1)
    10  INTO testit
    11  VALUES('BRID','C',2)
    12  INTO testit
    13  VALUES('BRID','C',3)
    14  INTO testit
    15  VALUES('BRID','C',4)
    16  INTO testit
    17  VALUES('BRID','C',5)
    18  INTO testit
    19  VALUES('BRID','B',1)
    20  INTO testit
    21  VALUES('BRID','B',2)
    22  INTO testit
    23  VALUES('BRID','B',3)
    24  INTO testit
    25  VALUES('BRID','B',4)
    26  INTO testit
    27  VALUES('BRID','B',5)
    28  INTO testit
    29  VALUES('BRID','B',6)
    30  INTO testit
    31  VALUES('BRID','B',7)
    32  SELECT * FROM dual;

    15 ROWS created.

    BING @ quanghoo >
    BING @ quanghoo > commit;

    Commit complete.

    BING @ quanghoo >
    BING @ quanghoo > SELECT col1, col2, col3
      2  FROM
      3  (SELECT col1, col2, col3, COUNT(*) OVER (ORDER BY col1) ct
      4   FROM testit)
      5  ORDER BY ct;

    COL1       C       COL3
    ---------- - ----------
    BRID       A          1
    BRID       A          2
    BRID       A          3
    BRID       C          1
    BRID       C          2
    BRID       C          3
    BRID       C          4
    BRID       C          5
    BRID       B          1
    BRID       B          2
    BRID       B          3
    BRID       B          4
    BRID       B          5
    BRID       B          6
    BRID       B          7

    15 ROWS selected.

    BING @ quanghoo >
    Of course you should test the code in YOUR environment to make sure it works as expected.