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

dharmadam wrote:
[color=blue]
> CREATE TABLE MYTABLE (
> BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
> INCREMENT BY 1),
> LAST_NAME CHAR(20));
>[/color]
insert into mytable(LAST_NAME)
(select bnf_last_nm from claimsa.tbeneficiary)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

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:
[color=blue]
> 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[/color]

--
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.

Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<c8ba70$c89$2@hanover.torolab.ibm.com>...[color=blue]
> dharmadam wrote:
>[color=green]
> > CREATE TABLE MYTABLE (
> > BENEF_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1
> > INCREMENT BY 1),
> > LAST_NAME CHAR(20));
> >[/color]
> insert into mytable(LAST_NAME)
> (select bnf_last_nm from claimsa.tbeneficiary)
>
> Cheers
> Serge[/color]


Thanks Serge. You have answer for everything.

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