Re: embedded SQL - how to insert with a GENERATED ALWAYS AS IDENTITY column

Sorry. Poor reading comprehension on my part :-)

Ugh! I missed that an OVERRIDING clause had been included in the quoted statement; for my having incorrectly expected the clause at the end [and probably for not being on a new line nor in caps], the clause was apparently expunged from my mind, thus why I suggested the clause OVERRIDING USER VALUE was required. I saw only the word DEFAULT replaced with the literal zero, as compared to the statement by Birgitta. Ugh again! My point about the OVERRIDING SYSTEM VALUE clause defeating the original intent still applies; problem there however, I apparently accidentally deleted a line of text before posting [now in "<ed:" below] which left what remained of my reply in conflict with itself.

Regards, Chuck

On 2/15/11 12:42 PM, Christen, Duane wrote:

I have this running in at least 75 - 100 production programs, and
they are working correctly. I started using identity columns, using
generated always, in V5R3, and have used this specification since
then.

There are two forms, overriding user value, and overriding system
value. Overriding user value replaces the user supplied value with
the system value and vice-versa.

From the 6.1 Infocenter SQL reference:

<<SNIP>>

CRPence on Tuesday, February 15, 2011 1:33 PM wrote:

Actually that variation on the SQL INSERT is not a valid option for
a GENERATED ALWAYS IDENTITY column, at least not with the OVERRIDING
USER VALUE clause, but with an override
<ed: to the generated value by specification of the
OVERRIDING SYSTEM VALUE clause>
, the identity value would not
be generated, and that is not likely the desired effect. The literal
zero versus the word DEFAULT asks\attempts to insert the numeric
value zero as the identity value for column TKTTKTDID, which would
require an override to the GENERATED value. The statement with the
numeric literal would fail with SQL0798 "Value cannot be specified
for GENERATED ALWAYS column TKTTKTDID."

On 2/15/11 10:32 AM, Christen, Duane wrote:

You can also do:

Exec SQL
Insert into pdatalib/TKT001DPF overriding user value
Values(:P1_SysID , :P2_AEmail , :P3_REmail,
:P4_TEmail , :P5_PrbName , :P6_Priority,
:P7_Status , :P8_TktType, :P9_Subject,
:PA_Body , :TICKET# , :TIMSTAMP,
0);

Although I will look into DEFAULT, as Birgitta suggested.

Birgitta Hauser on Tuesday, February 15, 2011 12:05 AM wrote:

Try:

Exec SQL
Insert into pdatalib/TKT001DPF VALUES
( :P1_SysID , :P2_AEmail , :P3_REmail,
:P4_TEmail , :P5_PrbName , :P6_Priority,
:P7_Status , :P8_TktType, :P9_Subject,
:PA_Body , :TICKET# , :TIMSTAMP,
DEFAULT) ;

<<SNIP>>