Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: Bugging SQL Statement behavior

    Hello, all.

    I hope someone can help with this issue I have been fighting for a while.

    I have the following SQL statement that SHOULD NOT update any rows:


    UPDATE PS_PO_HDR_S_WK_T4
    SET PO_AMT_APPR_NEEDED = 'Y'
    WHERE PROCESS_INSTANCE = 999999
    AND PO_STATUS IN ('A','O','PA', 'D')
    AND PO_AMT_APPR_ND_BU = 'Y'
    AND NOT EXISTS ( SELECT 'X' FROM PS_POBUILD_APPR POB
    WHERE POB.BUSINESS_UNIT =
    PS_PO_HDR_S_WK_T4.BUSINESS_UNIT AND POB.PO_PROCESS_ID =
    PS_PO_HDR_S_WK_T4.PO_PROCESS_ID AND POB.PO_AMT_APPR_NEEDED = 'N')


    Here is the thing. Table PS_PO_HDR_S_WK_T4 contains two rows with identical values for fields:
    - PROCESS_INSTANCE
    - PO_STATUS
    - PO_AMT_APPR_ND_BU
    - BUSINESS_UNIT
    - PO_PROCESS_ID

    Which means, both rows would match (or not) the criteria just the same.

    This UPDATE should not update ANY rows. I know this becuase if I run this select, no rows are returned (which is what I expect):


    SELECT * FROM PS_PO_HDR_S_WK_T4
    WHERE PROCESS_INSTANCE = 999999
    AND PO_STATUS IN ('A','O','PA', 'D')
    AND PO_AMT_APPR_ND_BU = 'Y'
    AND NOT EXISTS ( SELECT 'X' FROM PS_POBUILD_APPR POB
    WHERE POB.BUSINESS_UNIT =
    PS_PO_HDR_S_WK_T4.BUSINESS_UNIT AND POB.PO_PROCESS_ID =
    PS_PO_HDR_S_WK_T4.PO_PROCESS_ID AND POB.PO_AMT_APPR_NEEDED = 'N')


    But the SQL Update is updating one of the two rows!!!

    If the PS_PO_HDR_S_WK_T4 similar to these two, the SELECT will still return zero rows and the UPDATE will update 1 row.

    Can anyone see ANYTHING I am not seeing?

    Thanks a lot

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How did you found the updated row?

  3. #3
    Join Date
    May 2003
    Posts
    113
    which db2 or what release?

    it sounds like a bug and you should open a PMR to IBM.

    can you provide the DDL and data? it looks like a very interesting problem.

  4. #4
    Join Date
    Jun 2009
    Posts
    4
    Quote Originally Posted by tonkuma
    How did you found the updated row?
    By querying the updated table. Only one of the two rows got its PO_AMT_APPR_NEEDED updated to "Y"

  5. #5
    Join Date
    Jun 2009
    Posts
    4
    Quote Originally Posted by nidm
    which db2 or what release?

    it sounds like a bug and you should open a PMR to IBM.

    can you provide the DDL and data? it looks like a very interesting problem.
    I need to get back in the system and find that information.

    I will post it as soon as I can but it might not be until tomorrow.

    It is indeed an interesting problem that has been killing me for the past two days.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I suspected that the cause of your issue might be one of the followings.

    1) Bug of DB2.

    2) One of the two rows was PO_AMT_APPR_NEEDED = 'Y' before update.
    Because there was no condition "PS_PO_HDR_S_WK_T4.PO_AMT_APPR_NEEDED <> 'Y'" in your select.
    And PO_AMT_APPR_NEEDED was not included in the identical values for fields.
    Here is the thing. Table PS_PO_HDR_S_WK_T4 contains two rows with identical values for fields:
    - PROCESS_INSTANCE
    - PO_STATUS
    - PO_AMT_APPR_ND_BU
    - BUSINESS_UNIT
    - PO_PROCESS_ID
    Last edited by tonkuma; 06-10-09 at 15:07.

  7. #7
    Join Date
    Jun 2009
    Posts
    4
    Thing is the SQL Update returns a "1 Row Updated message". So it does update the row.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by pgarciajumela
    Thing is the SQL Update returns a "1 Row Updated message".
    You must be using some third party software to run your statements, thus introducing an extra source of possible errors. I would try to run the statements in the DB2 CLP.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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