Identity column

[dharmadam]

I have a table defined as

CREATE TABLE MYTABLE (
BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
INCREMENT BY 1),
LAST_NAME CHAR(20));

insert into mytable
(select sysibm.identity_val_local(),bnf_last_nm from
claimsa.tbeneficiary)

This gives the following error.
insert into mytable (select sysibm.identity_val_local(),bnf_last_nm
from claimsa.tbeneficiary)

DB21034E The command was processed as an SQL statement because it was
not a

valid Command Line Processor command. During SQL processing it
returned:

SQL0798N A value cannot be specified for column "BENEF_ID" which is
defined

as GENERATED ALWAYS. SQLSTATE=428C9

[Serge Rielau]

dharmadam wrote:

CREATE TABLE MYTABLE (
BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
INCREMENT BY 1),
LAST_NAME CHAR(20));

insert into mytable(LAST_NAME)
(select bnf_last_nm from claimsa.tbeneficiary)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Pierre Saint-Jacques]

The message is self-explanatory. You cannot specify a value for an
identity col. which generated always.
Db2 will start with value (1) in your case and increment as you go.

Your statement should then be:
insert into mytable (select bnf_last_nm from claimsa.tbeneficiary where
????????)
the function identity_val_local applies to retrieve the value of the
just inserted row in a table that has an identity col. defiend.
HTH, Pierre.

dharmadam wrote:

I have a table defined as

CREATE TABLE MYTABLE (
BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
INCREMENT BY 1),
LAST_NAME CHAR(20));

insert into mytable
(select sysibm.identity_val_local(),bnf_last_nm from
claimsa.tbeneficiary)

This gives the following error.
insert into mytable (select sysibm.identity_val_local(),bnf_last_nm
from claimsa.tbeneficiary)

DB21034E The command was processed as an SQL statement because it was
not a

valid Command Line Processor command. During SQL processing it
returned:

SQL0798N A value cannot be specified for column "BENEF_ID" which is
defined

as GENERATED ALWAYS. SQLSTATE=428C9

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[dharmadam]

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c8**********@hanover.torolab.ibm.com>...

dharmadam wrote:

CREATE TABLE MYTABLE (
BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
INCREMENT BY 1),
LAST_NAME CHAR(20));

insert into mytable(LAST_NAME)
(select bnf_last_nm from claimsa.tbeneficiary)

Cheers
Serge

Thanks Serge. You have answer for everything.

[Serge Rielau]

Tarrot Cards, DB2 edition :-)
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab