Hi All,

Greetings!!!

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?

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.

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

Sathyaram