IDENTITY_VAL_LOCAL() returns 2 rows

[Tim Dudgeon <td...@informaticsmatters.com>]

I have a table using the
GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY
autogenerated primary key.
I do an insert into this table and then try to get the generated key using
SELECT IDENTITY_VAL_LOCAL() FROM my_table
And I get two identical rows back.
Why 2 rows?

Tim

Re: IDENTITY_VAL_LOCAL() returns 2 rows

[Barry Books <tr...@gmail.com>]

sysibm.sysdummy1 has 1 row in it. If you do
select 1 from myTable
you will also get as many rows as are in the table. I often do
select * from myTable where key = IDENTITY_VAL_LOCAL() to get back
what's really in the table after triggers, default values etc.

If you don't have a where it's likely you will get more than 1 row.

Re: IDENTITY_VAL_LOCAL() returns 2 rows

[Dan Scott <de...@gmail.com>]

Use:

VALUES IDENTITY_VAL_LOCAL()

As the docs say: IDENTITY_VAL_LOCAL() is a "function that returns the
most recently assigned value of an identity column for a connection"
-- emphasis on _connection_. You can't select the latest value for a
specific table.

Dan

On 19/09/06, Tim Dudgeon <td...@informaticsmatters.com> wrote:
> Barry Books wrote:
> > try SELECT IDENTITY_VAL_LOCAL() from sysibm.sysdummy1
>
> Its get more weird!
> SELECT IDENTITY_VAL_LOCAL() from sysibm.sysdummy1
> returns 1 row
>
>
> SELECT IDENTITY_VAL_LOCAL() from the_actual_table_name
> returns as many rows as there are in the table, all with the value of
> the last ID.
>
> Tim
>
>

Re: IDENTITY_VAL_LOCAL() returns 2 rows

[Tim Dudgeon <td...@informaticsmatters.com>]

Barry Books wrote:
> try SELECT IDENTITY_VAL_LOCAL() from sysibm.sysdummy1

Its get more weird!
SELECT IDENTITY_VAL_LOCAL() from sysibm.sysdummy1
returns 1 row


SELECT IDENTITY_VAL_LOCAL() from the_actual_table_name
returns as many rows as there are in the table, all with the value of 
the last ID.

Tim

Re: IDENTITY_VAL_LOCAL() returns 2 rows

[Barry Books <tr...@gmail.com>]

try SELECT IDENTITY_VAL_LOCAL() from sysibm.sysdummy1