Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2014
    Posts
    3

    Unanswered: DB2 v10 for z/os Stored Procedure "FINAL TABLE"

    Hello,

    I am developing a Stored Procedure on DB2 v10 for z/os. My procedure works fine, for the most part, but this one issue is stumping me. I have code like this:

    select BUS_CLIENT_ID into bc_id from final table (insert into bus_client (BUS_CLIENT_ID, LAST_UPD_TS, LAST_UPD_USER_ID, BUS_CLIENT_ACT_TS, BUS_CLIENT_DEACT_TS, BUS_CLIENT_NAME, BUS_CLIENT_TYP_CD, BUS_CLIENT_TAX_ID, BUS_TYP_CD, PARENT_BUS_CLIENT_ID, ACT_STATUS_CD, BUS_CLIENT_IBP_CONVERT_TS)
    select next value for BUS_CLIENT_ID_SEQ, Current Timestamp, username, t.BUS_CLIENT_ACT_TS, t.BUS_CLIENT_DEACT_TS, name, t.BUS_CLIENT_TYP_CD, t.BUS_CLIENT_TAX_ID, t.BUS_TYP_CD, t.PARENT_BUS_CLIENT_ID, t.ACT_STATUS_CD, t.BUS_CLIENT_IBP_CONVERT_TS
    from bus_client t
    where bus_client_id = manager_id);

    It's supposed to copy a row into the same table, providing a different sequence ID, but it throws an SQLCODE=100, SQLSTATE=02000. I know the row it is selecting is there because I can select it with the same criteria and see the row. All the columns are populated, so there would be no NULLs to worry about. Here's the kicker. This same code structure works with other tables further down the stored procedure, so I know the syntax is correct. Also, when I remove "select id into bc_id from final table " from the statement, it does the insert, but I still don't have the ID that is needed further down the code. Any ideas why this code "select id into bc_id from final table " wouldn't work in the first table, but would work on other tables?
    Last edited by Mattwm; 09-18-14 at 11:28.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Is this the actual SQL you are executing or you have made some changes after copying it? I would think the above would give you a -206 as the column ID doesn't exist anywhere but your SELECT clause.
    Dave

  3. #3
    Join Date
    Sep 2014
    Posts
    3

    Edited my post

    Sorry, I should have been more careful editing the SQL. I had to cut out some proprietary information. I will edit the original post. The ID = BUS_CLIENT_ID.

  4. #4
    Join Date
    Sep 2014
    Posts
    3

    Found an acceptable solution

    I'm still not sure what the underlying problem is, but me and my team lead came up with an acceptable solution:

    select next value for BUS_CLIENT_ID_SEQ into bc_id from sysibm.SYSDUMMY1;
    insert into bus_client (BUS_CLIENT_ID, LAST_UPD_TS, LAST_UPD_USER_ID, BUS_CLIENT_ACT_TS, BUS_CLIENT_DEACT_TS, BUS_CLIENT_NAME, BUS_CLIENT_TYP_CD, BUS_CLIENT_TAX_ID, BUS_TYP_CD, PARENT_BUS_CLIENT_ID, ACT_STATUS_CD, BUS_CLIENT_IBP_CONVERT_TS)
    select bc_id, Current Timestamp, username, t.BUS_CLIENT_ACT_TS, t.BUS_CLIENT_DEACT_TS, name, t.BUS_CLIENT_TYP_CD, t.BUS_CLIENT_TAX_ID, t.BUS_TYP_CD, t.PARENT_BUS_CLIENT_ID, t.ACT_STATUS_CD, t.BUS_CLIENT_IBP_CONVERT_TS
    from bus_client t
    where bus_client_id = id;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed that a reason you want to keep next value into bc_id might be that you want to use the value later in other places.

    If my guess was right,
    you might want to use PREVIOUS VALUE FOR sequence-name to retrieve the value.


    IBM Knowledge Center | DB2 for z/OS 10.0.0>DB2 reference information>DB2 SQL>Language elements>Expressions>Sequence reference
    prevval-expression
    A PREVIOUS VALUE expression returns the most recently generated value for
    the specified sequence for a previous statement within the current application process.
    This value can be repeatedly referenced by using PREVIOUS VALUE expressions to specify the name of the sequence.
    There can be multiple instances of PREVIOUS VALUE expressions specifying the same sequence name
    within a single statement and they all return the same value.

    ...
    ...

Posting Permissions

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