I am doing some left outer joins on ibm mainframe db2:
DECLARE THD_CURSOR CURSOR FOR
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
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.
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 ).