Results 1 to 2 of 2

Thread: left outer join

  1. #1
    Join Date
    Dec 2005
    Posts
    1

    Question Unanswered: left outer join

    I am doing some left outer joins on ibm mainframe db2:

    DECLARE THD_CURSOR CURSOR FOR
    SELECT
    APP.REFER_NBR
    ,APP.LST_UPDT_TS
    ,CPRD.REFER_NBR
    ,CPRD.LST_UPDT_TS
    ,ADDR.REFER_NBR
    ,ADDR.LST_UPDT_TS
    FROM BCU_ADS_APPLICATN APP

    LEFT OUTER JOIN BCU_ADS_CARD_PROD CPRD
    ON CPRD.REFER_NBR = APP.REFER_NBR

    LEFT OUTER JOIN BCU_ADS_ADDRESS ADDR
    ON ADDR.REFER_NBR = APP.REFER_NBR

    WHERE APP.REFER_NBR = :WS-ADS-REFER-NBR

    FOR FETCH ONLY
    WITH UR
    END-EXEC


    I set the WS-ADS-REFER-NBR field to a value which matches a row in all three tables and the query works fine, however when I delete that row from the last table the query returns an sqlcode 100 (not found).

    I should still get rows returned but with null values for the fields from the third table.

    Any help to resolve??

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    I'd expect the same. - NULL-values for the third-table-columns.

    After deleting the row from table BCU_ADS_ADDRESS, did you check whether the row is still in table BCU_ADS_APPLICATN ?
    Might be there is a referential integrity defined with a cascading effect. ( Or a trigger which deletes the row from BCU_ADS_APPLICATN ).

Posting Permissions

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