RE: INSERTing datastructure into table with GENERATED ALWAYS column.

You can do this by creating a view that includes all columns except the identity column.

CREATE VIEW HJWMS/T_IMPORT_ITEM_MASTER_VIEW FOR SYSTEM NAME WIITMVIEW
(IMPORT_ID, IMPORT_STATUS)
AS (Select IMPORT_ID, IMPORT_STATUS from HJWMS/T_IMPORT_ITEM_MASTER) RCDFMT WIITM;

Then defined the Ds using the View:

Dcl-Ds testout4 Extname(' WIITMVIEW ') qualified End-ds;

And run the insert using the view:

exec sql insert into WIITMVIEW VALUES :testout4;

This still requires you to list all the columns in the view. And you would need to add/remove columns in the view every time they are added/removed from the table.

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