Re: SQL on V5R1

Correct, select from insert was added at 6.1

Charles

On Mon, Oct 19, 2015 at 9:50 AM, <rob@xxxxxxxxx> wrote:

I did read the whole message. I knew he was on 5.1.

I was commenting on your suggestion of a before insert trigger and the
downside of it. One being the lack of getting the value. Perhaps if I
had dropped the first part of the identity_val_local you might have made
it to the second part suggesting "select from insert" (which you snipped).
I was hoping that might be able to read a value inserted by a before
insert trigger. But I believe that support for "select from insert"
didn't come out until later also.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Charles Wilt <charles.wilt@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 10/19/2015 09:26 AM
Subject: Re: SQL on V5R1
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Rob,

Read the whole thread / message...

He's dealing with v5r1, which doesn't have IDENTITY and thus doesn't have
​
IDENTITY_VAL_LOCAL...

I suggested a BEFORE INSERT trigger could emulate some of the
functionality
butt that there would be downsides.

Charles

On Mon, Oct 19, 2015 at 9:02 AM, <rob@xxxxxxxxx> wrote:

Charles,

<snip>
Lots of downsides to this, including the fact that there'd be no built

in

way to let the program doing the write know what identity value was
generated.
</snip>

There are multiple ways of learning the last generated identity column.
One is by the scalar built in function
​​
IDENTITY_VAL_LOCAL as in
INSERT INTO EMPLOYEE
(NAME, SALARY, DEPTNO)
VALUES('Rupert', 989.99, 50)

VALUES IDENTITY_VAL_LOCAL() INTO :IVAR
I suspect this came out when, or after, support for GENERATED was added

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.