Selecting sequence inside row copy query.

asp_crazy_guy · #1 (**permalink**) 09-09-09, 01:09

Hello folks,

I have a scenario where I have to duplicate insert a record inside the same table. I am having problem in generating the serial for the insert query as the id field in the table is a sequence field.
Here is my query

Code:

INSERT INTO ADMINISTRATOR.UWPROPTRTY (GENTREATYSRNO, GENTREATYYEAR, GENTREATYCODE, GENSUBBUSICLASS, GENCLIENTCODE, GENCURRCODE, GENENTRYDATE, GENTREATYDATEFROM, GENTREATYDATETO, GENDESCRIPTION, GENPTRTYSTATUS, DATETIME, LASTMODIFYBY, TERMINALID, GENRENEWSTAT, PREVTRTSRNO)
 values (select (SELECT NEXTVAL FOR administrator.ptrt_seq as seqid FROM sysibm.sysdummy1), GENTREATYYEAR, GENTREATYCODE, GENSUBBUSICLASS, GENCLIENTCODE, GENCURRCODE, GENENTRYDATE, GENTREATYDATEFROM, GENTREATYDATETO, GENDESCRIPTION, GENPTRTYSTATUS, DATETIME, LASTMODIFYBY, TERMINALID, GENRENEWSTAT, PREVTRTSRNO from administrator.uwproptrty where gentreatysrno=261 )

Help me guys

Best regards, Mike.

tonkuma · #2 (**permalink**) 09-09-09, 01:19

What is your problem?

Marcus_A · #3 (**permalink**) 09-09-09, 01:19

I am not sure I understand what you are trying to do, but if want to find out what value was used in an insert with a NEXTVAL FOR sequence-name, then you should look at the FINAL TABLE syntax.

Select GENTREATYSRNO from FINAL TABLE (
INSERT INTO ADMINISTRATOR.UWPROPTRTY
(GENTREATYSRNO, GENTREATYYEAR, GENTREATYCODE, GENSUBBUSICLASS, GENCLIENTCODE, GENCURRCODE, GENENTRYDATE, GENTREATYDATEFROM, GENTREATYDATETO, GENDESCRIPTION, GENPTRTYSTATUS, DATETIME, LASTMODIFYBY, TERMINALID, GENRENEWSTAT, PREVTRTSRNO)
values (NEXTVAL FOR administrator.ptrt_seq, GENTREATYYEAR, GENTREATYCODE, GENSUBBUSICLASS, GENCLIENTCODE, GENCURRCODE, GENENTRYDATE, GENTREATYDATEFROM, GENTREATYDATETO, GENDESCRIPTION, GENPTRTYSTATUS, DATETIME, LASTMODIFYBY, TERMINALID, GENRENEWSTAT, PREVTRTSRNO)
);

asp_crazy_guy · #4 (**permalink**) 09-09-09, 01:26

I cannot select the next sequence as the primary key for the row I am inserting inside my select statement. Because select next val for seq)as id is not working with multiple select.

Quote:

Originally Posted by tonkuma

What is your problem?

asp_crazy_guy · #5 (**permalink**) 09-09-09, 01:28

Thanks for the reply Marcus. I am trying to insert a row again in the same table with a new serial. I have limitation in my table that the primary key is a sequence field so that's why I am trying to select nextval from the sequence so I could fetch the primary key to insert for the new row.

Quote:

Originally Posted by Marcus_A

I am not sure I understand what you are trying to do, but if want to find out what value was used in an insert with a NEXTVAL FOR sequence-name, then you should look at the FINAL TABLE syntax.

Select GENTREATYSRNO from FINAL TABLE (
INSERT INTO ADMINISTRATOR.UWPROPTRTY
(GENTREATYSRNO, GENTREATYYEAR, GENTREATYCODE, GENSUBBUSICLASS, GENCLIENTCODE, GENCURRCODE, GENENTRYDATE, GENTREATYDATEFROM, GENTREATYDATETO, GENDESCRIPTION, GENPTRTYSTATUS, DATETIME, LASTMODIFYBY, TERMINALID, GENRENEWSTAT, PREVTRTSRNO)
values (NEXTVAL FOR administrator.ptrt_seq, GENTREATYYEAR, GENTREATYCODE, GENSUBBUSICLASS, GENCLIENTCODE, GENCURRCODE, GENENTRYDATE, GENTREATYDATEFROM, GENTREATYDATETO, GENDESCRIPTION, GENPTRTYSTATUS, DATETIME, LASTMODIFYBY, TERMINALID, GENRENEWSTAT, PREVTRTSRNO)
);