Results 1 to 11 of 11
  1. #1
    Join Date
    May 2013
    Posts
    4

    Lightbulb Unanswered: next val for sequence

    I have a stored procedure that we want to use next val from sequence QSSRCBBS to insert that next val into a table ...

    INSERT INTO TABLE
    PLANT_CODE,
    BUY_NUMBER,
    ETC...
    VALUES
    :TABLE-PLANT-CODE,
    NEXTVAL FOR QSSRCBBS,
    ETC...

    How do I know what value was inserted? How can I use this value to insert a record into another table using the same stored procedure? Is there a way to do an (into :TABLE-BUY-NUMBER)

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    One might use the PREVIOUS VALUE (or PREVVAL) reference for that.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Select BUY_NUMBER from FINAL TABLE
    (INSERT INTO TABLE
    PLANT_CODE,
    BUY_NUMBER,
    ETC...
    VALUES
    :TABLE-PLANT-CODE,
    NEXTVAL FOR QSSRCBBS,
    ETC...)
    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
    May 2013
    Posts
    4
    Marcas A,

    What is the FINAL TABLE you reference?

    n_i,

    I will have the same dilemma with PREVVAL

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tmac_75 View Post
    Marcas A,

    What is the FINAL TABLE you reference?
    It works just the way I showed with the exact syntax I used. If you don't believe me, RTFM or try it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    tmac 75, DB2 (z/OS in V9 and LUW V9.7 (maybe earlier)) has a SELECT FROM Insert, Update, Delete (and possibly Merge). You reference the rows from the DML action as a Table called FINAL TABLE (the rows after the change) or OLD TABLE (the rows before the change). The Table used depends on the DML being executed.

    So the example Marcus_A supplied executes the Insert (and populates the row with the Sequence value and that row (or rows) are available when referenced as FINAL TABLE. So you are Selecting any row that was changed by the Insert (in your case).

  7. #7
    Join Date
    May 2013
    Posts
    4
    Thanks for the explanation. I'm not questioning whether or not it WORKS. geesh TACP

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tmac_75 View Post

    I will have the same dilemma with PREVVAL
    Dilemma implies alternatives; I don't see any dilemma in your question.

    Since you are doing this in the context of a stored procedure, nothing prevents you from assigning NEXTVAL (or PREVVAL after the first insert) to a variable and using that variable in other statements.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    May 2013
    Posts
    4

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    Quote Originally Posted by tmac_75 View Post
    I have a stored procedure that we want to use next val from sequence QSSRCBBS to insert that next val into a table ...

    INSERT INTO TABLE
    PLANT_CODE,
    BUY_NUMBER,
    ETC...
    VALUES
    :TABLE-PLANT-CODE,
    NEXTVAL FOR QSSRCBBS,
    ETC...

    How do I know what value was inserted? How can I use this value to insert a record into another table using the same stored procedure? Is there a way to do an (into :TABLE-BUY-NUMBER)
    FYI: It's Better to make this column (BUY_NUMBER) as generated (Always, or by Default) and ommit from Inserted Columns List.
    In this case you'll have sequence numbers like start, start + 1, etc.

    Lenny

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Lenny77 View Post
    FYI: It's Better to make this column (BUY_NUMBER) as generated (Always, or by Default) and ommit from Inserted Columns List.
    In this case you'll have sequence numbers like start, start + 1, etc.

    Lenny
    Why is it better?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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