Yet another query question...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shorti - 26 Sep 2007 00:42 GMT

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.

Reply to this Message

Lennart - 26 Sep 2007 04:12 GMT

> 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
[quoted text clipped - 9 lines]
> 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

Reply to this Message

Serge Rielau - 26 Sep 2007 16:31 GMT

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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

shorti - 27 Sep 2007 19:42 GMT

> > 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
[quoted text clipped - 23 lines]
> 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?

Reply to this Message