Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: INSERT with Delete query

    Hi

    We have requirement like whatever row deleted in the below query should insert to the
    another table(REPORT_TABLE)
    Code:
    delete from MAIN_TABLE T1 
    where T1.policy_LOC ='AA' 
    and RECV_DT < (select MAX(T2.RECV_DT) 
          from MAIN_TABLE T2
    where 
        T2.policy_LOC=T1.policy_LOC
    AND T2.POLICY_NO =T1.POLICY_NO
    AND T2.RECV_DT < CURRENT_DATE 
    
    GROUP BY
    
    T2.policy_LOC
    ,T2.POLICY_NO
      )
    MAIN_TABLE column details

    POLICY_LOC
    ,POLICY_NO
    ,HOLDER_NAME
    ,HOLDER_AGE
    ,MISC-TXT
    ,LOGIN_NAME
    ,PROGRAM_ID
    ,RECV_DT
    ,LAST_TIMESTAMP


    REPORT_TABLE column details

    POLICY_LOC
    ,POLICY_NO
    ,HOLDER_NAME
    ,HOLDER_AGE
    ,PROGRAM_ID
    ,RECV_DT
    ,LAST_TIMESTAMP
    ,PROCESS_TYPE


    In the REPORT_TABLE should have whatever row is deleted from the MAIN_TABLE
    with PROCESS_TYPE column as 'DEL' literal value and LAST_TIMESTAMP should have current Time stamp value
    and we have to pass the Program name(RHRESAER) literaly to PROGRAM_ID column

    MAIN_TABLE
    Code:
    POLICY_LOC   POLICY_NO HOLDER_NAME  HOLDER_AGE  MISC_TXT LOGIN_NAME PROGRAM_ID   RECV_DT        LAST_TIMESTAMP
    
    AA            1234      STEVE        34           PREMI    DERE       FXTYUYER    2011-10-19   2011-10-11 14:46:54.511682
    AA            1234      STEVE        34           PREMI    DERE       FXTYUYER    2011-11-10   2011-10-13 12:46:54.411683
    AA            2345      ROBIN        44           PREMI    DERE       FXTYUYER    2011-10-30   2011-10-15 13:47:44.614681 
    AA            2345      ROBIN        44           PREMI    DERE       FXTYUYER    2011-11-10   2011-10-21 13:47:45.314682
    BB            9999      JAMES        24           PREMI    DERE       FXTYUYER    2011-12-21   2011-09-19 12:46:54.411683
    REPORT_TABLE should have
    Code:
    POLICY_LOC   POLICY_NO HOLDER_NAME  HOLDER_AGE   PROGRAM_ID   RECV_DT        LAST_TIMESTAMP             PROCESS_TYPE
    
    AA            1234      STEVE        34           RHRESAER    2011-10-19   2011-11-10 14:46:54.511682    DEL
    AA            2345      ROBIN        44           RHRESAER    2011-10-30   2011-11-10 13:47:44.614681    DEL
    Please help me on that..how to write INSERT query ?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try like this...
    (not tested)
    Code:
    INSERT INTO report_table
    SELECT ...
     FROM  OLD TABLE
           (delete from MAIN_TABLE T1
             where T1.policy_LOC ='AA'
               and RECV_DT
                   < (select MAX(T2.RECV_DT) 
                        from MAIN_TABLE T2
                       where
                             T2.policy_LOC = T1.policy_LOC
                         AND T2.POLICY_NO  = T1.POLICY_NO
                         AND T2.RECV_DT    < CURRENT_DATE
                       GROUP BY
                             T2.policy_LOC
                           , T2.POLICY_NO
                     )
           ) d
    ;

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the quick reply


    INSERT INTO report_table
    SELECT ...
    FROM OLD TABLE
    Please clarify..What do you mean byOLD TABLE ?
    means like below
    Code:
    INSERT INTO report_table
    SELECT *
     FROM  MAIN_TABLE

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please read the description of OLD TABLE in Data change table references in Information Center
    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

    OLD TABLE

    Specifies that the rows of the intermediate result table represent the set of rows that are changed by the SQL data change statement as they existed prior to the application of the data change statement.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Billa007 View Post
    Please clarify..What do you mean byOLD TABLE ?
    Look at the last few slides from this guy's presentation: http://www.gse-nordic.org/Working%20...2%20Stream/s44
    Once you found the answer to your question take some time to look at the whole presentation. Very informative.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried my sample INSERT statement and got an error.
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL20165N An SQL data change statement within a FROM clause is not allowed
    in the context in which it was specified. SQLSTATE=428FL
    Then, after some trial the following statement was successful.
    Note1: I removed some columns from both tables to simplify my test.
    Note2: I changed "AND T2.RECV_DT < CURRENT_DATE" to "AND T2.RECV_DT <= CURRENT_DATE" to delete/insert two rows.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM Billa007.main_table
    ;
    ------------------------------------------------------------------------------
    
    POLICY_LOC   POLICY_NO PROGRAM_ID RECV_DT    LAST_TIMESTAMP            
    ------------ --------- ---------- ---------- --------------------------
    AA                1234 FXTYUYER   2011-10-19 2011-10-11-14.46.54.511682
    AA                1234 FXTYUYER   2011-11-10 2011-10-13-12.46.54.411683
    AA                2345 FXTYUYER   2011-10-30 2011-10-15-13.47.44.614681
    AA                2345 FXTYUYER   2011-11-10 2011-10-21-13.47.45.314682
    BB                9999 FXTYUYER   2011-12-21 2011-09-19-12.46.54.411683
    
      5 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    WITH
     delete_main AS (
    SELECT *
     FROM  OLD TABLE
           (delete from Billa007.MAIN_TABLE T1
             where T1.policy_LOC ='AA'
               and RECV_DT
                   < (select MAX(T2.RECV_DT) 
                        from Billa007.MAIN_TABLE T2
                       where
                             T2.policy_LOC = T1.policy_LOC
                         AND T2.POLICY_NO  = T1.POLICY_NO
                         AND T2.RECV_DT    <= CURRENT_DATE
                       GROUP BY
                             T2.policy_LOC
                           , T2.POLICY_NO
                     )
           ) d
    )
    , insert_report AS (
    SELECT *
     FROM  FINAL TABLE
           (INSERT INTO Billa007.report_table
            SELECT policy_loc
                 , policy_no
                 , 'RHRESAER'
                 , recv_dt
                 , CURRENT_TIMESTAMP
                 , 'DEL'
             FROM  delete_main
           )
    )
    SELECT *
     FROM  insert_report
    ;
    ------------------------------------------------------------------------------
    
    POLICY_LOC   POLICY_NO PROGRAM_ID RECV_DT    LAST_TIMESTAMP             PROCESS_TYPE
    ------------ --------- ---------- ---------- -------------------------- ------------
    AA                1234 RHRESAER   2011-10-19 2011-11-10-21.06.25.258000 DEL         
    AA                2345 RHRESAER   2011-10-30 2011-11-10-21.06.25.258000 DEL         
    
      2 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM Billa007.main_table;
    ------------------------------------------------------------------------------
    
    POLICY_LOC   POLICY_NO PROGRAM_ID RECV_DT    LAST_TIMESTAMP            
    ------------ --------- ---------- ---------- --------------------------
    AA                1234 FXTYUYER   2011-11-10 2011-10-13-12.46.54.411683
    AA                2345 FXTYUYER   2011-11-10 2011-10-21-13.47.45.314682
    BB                9999 FXTYUYER   2011-12-21 2011-09-19-12.46.54.411683
    
      3 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM Billa007.report_table;
    ------------------------------------------------------------------------------
    
    POLICY_LOC   POLICY_NO PROGRAM_ID RECV_DT    LAST_TIMESTAMP             PROCESS_TYPE
    ------------ --------- ---------- ---------- -------------------------- ------------
    AA                1234 RHRESAER   2011-10-19 2011-11-10-21.06.25.258000 DEL         
    AA                2345 RHRESAER   2011-10-30 2011-11-10-21.06.25.258000 DEL         
    
      2 record(s) selected.

  7. #7
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the effort;

    The same error happened for me
    "
    SQL20165N An SQL data change statement within a FROM clause is not allowed
    in the context in which it was specified. SQLSTATE=428FL

    SQL20165N An SQL data change statement within a FROM clause is not allowed in the context in which it was specified.
    "

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you received the error from my Example 1,
    what DB2 Version, fixpack and Edition + your Operating System(including version info) are you using?

    I used...
    Code:
    :\IBM\SQLLIB_V97\tools>db2level
    DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09074" with 
    level identifier "08050107".
    Informational tokens are "DB2 v9.7.400.501", "s110330", "IP23237", and Fix Pack 
    "4".
    Product is installed at "D:\IBM\SQLLIB_V97" with DB2 Copy Name "DB2COPY1".
    
    D:\IBM\SQLLIB_V97\tools>db2licm -l 
    Product name:                     "DB2 Express-C"
    License type:                     "Unwarranted"
    Expiry date:                      "Permanent"
    Product identifier:               "db2expc"
    Version information:              "9.7"
    Max number of CPUs:               "2"
    Max amount of memory (GB):        "2"
    If you tried my first example,
    please try Example 1.

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    I tried in SPUFI tool also..error comes like below
    "DSNT408I SQLCODE = -20165, ERROR: AN SQL DATA CHANGE STATEMENT WITHIN A FROM
    CLAUSE IS NOT ALLOWED IN THE CONTEXT IN WHICH IT WAS SPECIFIED
    DSNT418I SQLSTATE = 428FL SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNXOP0 SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
    "
    Version is
    DB2 SQL PRECOMPILER VERSION 9 REL. 1.0

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Looking into manuals,
    DB2 for z/OS seems more ristrictive about using data-change-table-reference.

    in DB2 Version 9.1 for z/OS "Codes"
    -20165 AN SQL DATA CHANGE STATEMENT
    WITHIN A FROM CLAUSE IS NOT
    ALLOWED IN THE CONTEXT IN
    WHICH IT WAS SPECIFIED

    Explanation:
    ...
    ... The SQL data
    change statement must be the only table-reference in
    the FROM clause that is used in:
    v the outer fullselect of a SELECT statement, that is a
    subselect
    v a SELECT INTO statement
    while
    in DB2 9.1 for LUW "Message Reference, Volume 2"
    SQL20165N An SQL data change statement within
    a FROM clause is not allowed in the context
    in which it was specified.

    Explanation:
    ...
    ... The SQL data change statement must be the only
    table-reference in the FROM clause that is used in:
    v the outer fullselect of a SELECT statement
    v a SELECT INTO statement
    v the outer fullselect of a common table expression
    v the only fullselect in an assignment statement.
    DB2 for LUW includes support for "data change statement" in "the outer fullselect of a common table expression",
    but DB2 for z/OS doesn't.

    I don't know much about DB2 for z/OS than DB2 for LUW.
    So, I'm sorry that I can't help you more.

    My another idea is to use the delete statement as a table reference in a cursor, then fetch the cursor and insert into report_table,
    though I don't know whather this idea work on DB2 for z/OS.

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the effort,

    What i was trying is ..First performed the select query and trying to insert to the another table and do perform the delete process like below

    Code:
    Insert into Billa007.report_table
    select from Billa007.MAIN_TABLE T1
             where T1.policy_LOC ='AA'
               and RECV_DT
                   < (select MAX(T2.RECV_DT) 
                        from Billa007.MAIN_TABLE T2
                       where
                             T2.policy_LOC = T1.policy_LOC
                         AND T2.POLICY_NO  = T1.POLICY_NO
                         AND T2.RECV_DT    <= CURRENT_DATE
                       GROUP BY
                             T2.policy_LOC
                           , T2.POLICY_NO
                     )
    and again do the perform the delete query..
    but i am facing the below error

    'SQL0412N Multiple columns are returned from a subquery that is allowed only
    one column. SQLSTATE=42823 "

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is the code you showed realy what you executed?
    Because, no column list in "select from Billa007.MAIN_TABLE T1".

  13. #13
    Join Date
    Sep 2011
    Posts
    220
    Tried below query
    Code:
    Insert into Billa007.report_table(
    POLICY_LOC
    ,POLICY_NO
    ,HOLDER_NAME
    ,HOLDER_AGE
    ,RECV_DT
    ,LAST_TIMESTAMP
    ,PROGRAM_ID
    ,PROCESS_TYPE)
    
    select
     
    POLICY_LOC
    ,POLICY_NO
    ,HOLDER_NAME
    ,HOLDER_AGE
    ,RECV_DT
    ,CURRENT_TIMESTAMP
    ,'RHRESAER'
    , 'DEL'
    
      from Billa007.MAIN_TABLE T1
    :
    :
    :
    
          , T2.POLICY_NO
                     )
    Received error like below
    SQLSTATE=22007

    SQL0180N The syntax of the string representation of a datetime value is incorrect.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why didn't you show full code without omission?

    Anyhow, show the create table statements of Billa007.MAIN_TABLE and Billa007.report_table.

  15. #15
    Join Date
    Sep 2011
    Posts
    220
    resolved the Datetime error..

    please propose if any alternate way to insert the rows to another table whatever rows deleted in the table
    Thanks again

Posting Permissions

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