Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007

    Question Unanswered: PROBLEM using INDENTITY_VAL_LOCAL() function with sub queries in VALUES clause

    Hi All,


    I'm very new to this group and i'm facing a problem with using INDENTITY_VAL_LOCAL() function to get the last value of the identity column. The function returns null when i use sub queries (SELECTs) in the VALUES clause. For example, running the following sqls return the correct value of the identity column.

    INSERT INTO table2(column2, column3) VALUES('value1','value2');

    SELECT IDENTITY_VAL_LOCAL() as LastCreatedValue, COUNT(*) as dummy from table2;

    However, the IDENTITY_VAL_LOCAL() function returns null if i use SELECT in the VALUES clause.

    INSERT INTO table2(column2, column3) VALUES((SELECT column1 FROM table1 WHERE column2='TEST'),'value2');

    SELECT IDENTITY_VAL_LOCAL() as LastCreatedValue, COUNT(*) as dummy from table2;

    I'm using DB2 8.1.14 version. Can anyone tell me what the problem may be?

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    I don't know the answer to your question, but why don't you simply select the results of your INSERT operation? This will work with your subselect as well.
    SELECT id
    FROM   NEW TABLE ( INSERT INTO table2(column2, column3) VALUES ( ... ) ) AS t(id, column2, column3)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2001
    Refere the manual ... There are a number of cases when the function will not work ... IIRC, your case is also documented.

    As you are FP9+, you should be using Knut's suggestion

    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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