Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Unanswered: Selecting sequence inside row copy query.

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is your problem?

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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)
    );
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Mar 2009
    Posts
    73
    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?

  5. #5
    Join Date
    Mar 2009
    Posts
    73
    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)
    );

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The syntax of your INSERT is incorrect. Check the manual.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Mar 2009
    Posts
    73

    Hi..

    Hello thanks for the replies,

    The "values" in insert statement was creating problems. After I removed it its working fine now . Thanks to you folks I am saved from my boss once again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •