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??

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 ).