Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: Split the first record in duplicate records into 1 temp table

    Hi Team,

    My requirement is like below, please help me.
    i have some set of records, in that on grouping columns i need to send first record to one temp table and rest all records to other temp table in my stored procedure.
    Ex:

    Input:
    A B C D
    1 xx ABC 345
    1 xx ABC 234
    2 YY BCD 567
    2 YY BCD 123
    3 ZZ CDE 678

    First temp table records:
    A B C D
    1 xx ABC 345
    2 YY BCD 567
    3 ZZ CDE 678

    Second temp table records:
    A B C D
    1 xx ABC 234
    2 YY BCD 123

    My grouping columns are A,B,C

    Please help me how to do this...

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There is no implicit sequence in tables of Relational DB(including DB2).
    You may want to specify the sequence of a table by the value(s) of column(s).
    I used "ORDER BY d DESC" in the following example.

    Prepare test tables and populate input table.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE madsongtel.Input
    ( A SMALLINT   NOT NULL
    , B VARCHAR(3) NOT NULL
    , C VARCHAR(4) NOT NULL
    , D SMALLINT   NOT NULL
    );
    
    CREATE TABLE madsongtel.First_temp
    ( A SMALLINT   NOT NULL
    , B VARCHAR(3) NOT NULL
    , C VARCHAR(4) NOT NULL
    , D SMALLINT   NOT NULL
    );
    
    CREATE TABLE madsongtel.Second_temp
    ( A SMALLINT   NOT NULL
    , B VARCHAR(3) NOT NULL
    , C VARCHAR(4) NOT NULL
    , D SMALLINT   NOT NULL
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO madsongtel.Input
    VALUES
      ( 1 , 'xx' , 'ABC' , 345 )
    , ( 1 , 'xx' , 'ABC' , 234 )
    , ( 2 , 'YY' , 'BCD' , 567 )
    , ( 2 , 'YY' , 'BCD' , 123 )
    , ( 3 , 'ZZ' , 'CDE' , 678 )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Insert to First_temp table and Second_temp.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sequenced_input AS (
    SELECT a , b , c , d
         , ROW_NUMBER()
              OVER( PARTITION BY a , b , c
                        ORDER BY d DESC
                  ) AS rnum
     FROM  madsongtel.Input
    )
    , insert_first AS (
    SELECT COUNT(*) AS count_first_temp
     FROM  FINAL TABLE
           (INSERT INTO madsongtel.first_temp
            SELECT a , b , c , d
             FROM  sequenced_input
             WHERE rnum = 1
           )
    )
    , insert_second AS (
    SELECT COUNT(*) AS count_second_temp
     FROM  FINAL TABLE
           (INSERT INTO madsongtel.second_temp
            SELECT a , b , c , d
             FROM  sequenced_input
             WHERE rnum > 1
           )
    )
    SELECT *
     FROM  insert_first
     CROSS JOIN
           insert_second
    ;
    ------------------------------------------------------------------------------
    
    COUNT_FIRST_TEMP COUNT_SECOND_TEMP
    ---------------- -----------------
                   3                 2
    
      1 record(s) selected.
    See the results of First_temp table and Second_temp table.
    Code:
    SELECT * FROM madsongtel.first_temp
    
    A      B   C    D     
    ------ --- ---- ------
         1 xx  ABC     345
         2 YY  BCD     567
         3 ZZ  CDE     678
    
      3 record(s) selected.
    
    
    SELECT * FROM madsongtel.second_temp
    
    A      B   C    D     
    ------ --- ---- ------
         1 xx  ABC     234
         2 YY  BCD     123
    
      2 record(s) selected.

  3. #3
    Join Date
    May 2011
    Posts
    11
    Thank you Tonkuma!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •