I am inserting a record into a table that has an identity column that
does a GENERATED ALWAYS. It is the only column in the table that
gives the record its uniqueness. I want to capture this number when I
insert a record. How can I do it in one step?

I found this query in the DB2 Info Center. It does some other things
but I tried to convert it to what I needed and couldnt get it to
work. Here is the Info Center query:

SELECT inorder.ordernum
FROM (INSERT INTO orders(custno)INCLUDE (insertnum integer)
VALUES(:cnum1, 1), (:cnum2, 2)) InsertedOrders
ORDER BY insertnum;

I tried :

SELECT t1.col1
FROM (INSERT INTO t1 (col2, col3, col4) VALUES (1, 2, 3))

I keep getting an SQL0104N An unexpected token.....Expected tokens may
include: "<space>" after the "SELECT t1.col1 FROM("

Any help would be appreciated.

On Sep 26, 1:42 am, shorti <lbrya...@juno.com> wrote:
> I am inserting a record into a table that has an identity column that
> does a GENERATED ALWAYS. It is the only column in the table that
> gives the record its uniqueness. I want to capture this number when I
> insert a record. How can I do it in one step?
>
> I found this query in the DB2 Info Center. It does some other things
> but I tried to convert it to what I needed and couldnt get it to
> work. Here is the Info Center query:
>
> SELECT inorder.ordernum
> FROM (INSERT INTO orders(custno)INCLUDE (insertnum integer)
> VALUES(:cnum1, 1), (:cnum2, 2)) InsertedOrders
> ORDER BY insertnum;
>

I'm not sure I undestand your sql, but something along the lines of:

SELECT InsertedOrders.ordernum
FROM new table (
INSERT INTO orders(custno)
VALUES(:cnum1), (:cnum2)
) InsertedOrders (ordernum, custno)
ORDER BY insertnum;


/Lennart

shorti wrote:
> I am inserting a record into a table that has an identity column that
> does a GENERATED ALWAYS. It is the only column in the table that
> gives the record its uniqueness. I want to capture this number when I
> insert a record. How can I do it in one step?
Here is a quick example showing SELECT FORM INSERT:

CREATE TABLE T(ID INT GENERATED ALWAYS AS IDENTITY,
C2 INT,
C3 INT GENERATED ALWAYS AS (c2 + 10),
c4 INT);

CREATE TRIGGER trg BEFORE INSERT ON T
REFERENCING NEW AS n FOR EACH ROW
SET c4 = c2 * 10;

SELECT id, c3, c4 FROM NEW TABLE(INSERT INTO T(c2) VALUES 5);
ID C3 C4
----------- ----------- -----------
1 15 50

1 record(s) selected.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Sep 26, 8:31 am, Serge Rielau <srie...@ca.ibm.com> wrote:
> shorti wrote:
> > I am inserting a record into a table that has an identity column that
> > does a GENERATED ALWAYS. It is the only column in the table that
> > gives the record its uniqueness. I want to capture this number when I
> > insert a record. How can I do it in one step?
>
> Here is a quick example showing SELECT FORM INSERT:
>
> CREATE TABLE T(ID INT GENERATED ALWAYS AS IDENTITY,
> C2 INT,
> C3 INT GENERATED ALWAYS AS (c2 + 10),
> c4 INT);
>
> CREATE TRIGGER trg BEFORE INSERT ON T
> REFERENCING NEW AS n FOR EACH ROW
> SET c4 = c2 * 10;
>
> SELECT id, c3, c4 FROM NEW TABLE(INSERT INTO T(c2) VALUES 5);
> ID C3 C4
> ----------- ----------- -----------
> 1 15 50
>
> 1 record(s) selected.
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

I have a couple of questions on this. What is the trigger for? I
tried the SELECT without the trigger and it works the same. I guess
it is unrelated. Also, FROM NEW TABLE is creating a new temporary
table called NEW?