Re: AW: Identity columns and RLA

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi John

I think the main problem is, Identiry_val_local is a function - so you 
need the parentheses, like this -

identity_val_local()

As the message says, it's not a column name and it's not a global variable.

On a little different bent - I wonder what would happen if you had 
created a data structure using LIKEREC for IO with this table - that's 
why some suggest having an LF that has all the columns except the 
identity one - but maybe a WRITE using DS just ignores whatever you put 
in that last column - if it's even there!

It's Friday - YOU can do more testing, right?   LOL

Vern

On 9/2/2016 9:37 AM, JRusling@xxxxxxxxxxx wrote:
> I was playing with identity columns (cuz it's Friday)...
>
> I created a simple table as below (using strsql on greenscreen)...
>
> CREATE TABLE johnr/MYTAB (
>    CNCUST  DECIMAL(6, 0) NOT NULL DEFAULT 0 ,
>    CNDESC  CHAR(30)      NOT NULL DEFAULT '' ,
>    CNIDCOL INTEGER GENERATED ALWAYS AS IDENTITY)
> Table MYTAB in JOHNR created but was not journaled.
>
> And have a simple rpg program with an F-spec using WRITE to write records
> to MYTAB...
>
>       fmytab     uf a e           k disk    rename(MYTAB:MYTABR)
>
> It works and the identity increments as expected.
>
> I tried to add the piece indicated in this thread about retrieving the
> identity value after the write operation but that piece is not working for
> me...
>
>       d gLastId         s             10i 0
>
>             cnCUST = s1CUST;
>             cnDESC = s1DESC;
>             write mytabr;
>             exec sql set :gLastId = Identity_Val_Local;
>             s1MSG = 'Record identity ' + %char(gLastId) + ' written.';
>
> the rpg variable gLastId is always zero, and the sqlcod after the exec sql
> statement is
>
> SQL0206 Column or global variable  not found.
>
> Any idea what I'm missing or doing wrong?
>
> Thanks,
> John
>
> <snip>
> There is no need to convert everything into SQL.
> If the identity column is generated always, the new identity value is
> determined correctly when writing a new record.
> If you perform an SQL SET statement, with IDENTITY_VAL_LOCAL, the identity
> value of your write operation.
> Exec SQL SET :LastId = Identity_Val_Local;
> </snip>
> <br />
> The information in this email is confidential and may be legally privileged.
> It is intended solely for the addressee. Access to this email by anyone else is
> unauthorized. If you are not the intended recipient, any disclosure, copying,
> distribution or any action taken or omitted to be taken in reliance on it, is
> prohibited and may be unlawful.