I'm quite new to DB2 and I have problem with Merge and
IDENTITY_VAL_LOCAL.

Sql looks like this:

MERGE INTO table1 oo
USING (SELECT 'somechar' AS NAZWA FROM SYSIBM.SYSDUMMY1) nn
ON oo.NAZWA = nn.NAZWA
WHEN NOT MATCHED THEN
insert (ID,NAZWA) values (DEFAULT,'somechar')
else ignore;

values IDENTITY_VAL_LOCAL()
always returns NULL even when insert is performed.

However calling "insert into table1 (...) values (...)" returns proper
value

What to do in order to make it work?

DB2 9.7.4 Express-C

--
Adam

On Jun 15, 3:27*pm, AD <ada... (AT) gmail (DOT) com> wrote:
Hi Adam,

IDENTITY_VAL_LOCAL function only works for single row INSERT statement
with VALUES clause:
http://publib.boulder.ibm.com/infoce.../r0004231.html

What can you do here is to use data-change-table-reference in your
subselect:
http://publib.boulder.ibm.com/infoce.../r0000875.html
like this:

select ID
from new table (
insert into table1 (NAZWA)
select NAZWA from table(values 'somechar') s(NAZWA)
where not exists (select 1 from table1 t where t.NAZWA=s.NAZWA)
)

Sincerely,
Mark B.