Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    25

    Unanswered: Inserting Latest Record.

    Below is my table struture call TABLE_A

    PRODUCT_ID INVOICE_NBR CREATION_DT CHARGE_DT INVOICE_AMT IFEE INVOICE_STATUS
    805526906 F1358412 5/29/2001 5/1/2001 0 INITIAL PSACCPT
    805526906 F1670427 7/30/2008 7/1/2008 5000 INITIAL PSACCPT
    808639021 F1520056 7/25/2005 7/1/2005 0 INITIAL PSACCPT
    808639021 F1663752 6/5/2008 6/1/2008 5000 INITIAL PSACCPT


    Now I have created one temporary table call TEMP in which I need to INSERT data from ablove table(TABLE_A).

    I need to insert all the fields from above table but the it should be latest invoice charged(CHARGE_DT) on Particular _Product.


    Please tell me the syntax or inbuilt function that I can use to grab the latest record in my INSERT statement.
    This is just a sample data, but I have many columns from different tables to be reported so GROUP BY cluase will be not a feasible approach.
    Inshort I need below two rows to be inserted in my TEMP table.




    PRODUCT_ID INVOICE_NBR CREATION_DT CHARGE_DT INVOICE_AMT INITIAL INVOICE_STATUS
    805526906 F1670427 7/30/2008 7/1/2008 5000 INITIAL PSACCPT
    808639021 F1663752 6/5/2008 6/1/2008 5000 INITIAL PSACCPT
    Last edited by smartcooldevil; 07-12-11 at 14:28.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    declare global temporary table x (product_id int, charge_dt date) on commit preserve rows not logged with replace;
    
    insert into session.x 
    select product_id,max(charge_dt) 
    from table_a
    group by product_id;
    
    insert into temp 
    select * from table a
    where (product_id,charge_dt) in (select product_id,charge_dt from session.x);
    Andy

  3. #3
    Join Date
    Jun 2010
    Posts
    25
    Thanks Andy,

    One more question.
    Can this be done without any extra temporaray table?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can try this:

    Code:
    insert into TEMP
    with t1 (product_id,charge_dt) as
    (select product_id,max(charge_dt) from table_a group by product_id)
    select * from table_a 
    where (product_id,charge_dt) in (select product_id,charge_dt from t1)
    Andy

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example (a):
    Code:
    INSERT INTO temp
    SELECT product_id , invoice_nbr , creation_dt , charge_dt
         , invoice_amt , ifee , invoice_status
     FROM  (SELECT a.*
                 , ROW_NUMBER()
                      OVER(PARTITION BY product_id
                               ORDER BY charge_dt DESC) AS r_n
             FROM  table_a a
           ) a
     WHERE r_n = 1
    ;
    See this thread too.
    http://www.dbforums.com/db2/1667188-...ves-error.html

    In Example (a),
    PARTITION BY was addded to select within each product_id
    and ROW_NUMBER was used instead of RANK to gurantee only one row to be selected for each product_id.

    Use ROW_NUMBER or RANK depending on your requirements.
    Last edited by tonkuma; 07-12-11 at 15:40.

Posting Permissions

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