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

[CRPence <CRPbottle@xxxxxxxxx>]

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