RE: INSERTing datastructure into table with GENERATED ALWAYS column.

Looks like you need to define your DS with Extname(..:*OUTPUT)
By adding OUTPUT you tell the system to not include the generated always column in the data structure.

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Rogerson
Sent: Monday, January 23, 2017 10:00 AM
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Subject: INSERTing datastructure into table with GENERATED ALWAYS column.

Hi All,

I have a table with a unique_id column defined as GENERATED ALWAYS.

CREATE TABLE HJWMS/T_IMPORT_ITEM_MASTER FOR SYSTEM NAME WIITM (
UNIQUE_ID INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
NO CACHE ) ,
IMPORT_ID VARCHAR(60) ALLOCATE(10) CCSID 37 NOT NULL ,
IMPORT_STATUS FOR COLUMN IMP_STATUS VARCHAR(10) ALLOCATE(10) CCSID
37 NOT NULL ,
...
CONSTRAINT HJWMS/Q_HJWMS_WIITM_UNIQUE_ID_00001 PRIMARY KEY( UNIQUE_ID ) )

RCDFMT WIITM ;

The data structure is defined as

D wiitm_Ds...
D e ds extname(wiitm)
D qualified inz


My insert statement is

Exec sql
INSERT INTO wiitm VALUES :wiitm_Ds;

When I try to compile the program I get the errors

SQL0117: Statement contains wrong number of values.

SQL0798: Position 1 Value cannot be specified for GENERATED ALWAYS column UNIQUE_ID.


Without listing all the columns individually on the insert statement is
there a way to insert the data structure?

Thanks,

Rob