select R.*, CAST(NVL(Expended,0) as I) as expended from rta_elements R LEFT JOIN ( select sum(amount) as Expended, Sponsor_ID ; from tip ; inner join ledger on ledger.tip_id = tip.tip_id and source="RTA" ; group by Sponsor_ID) MyAmount ON R.ElementNum = val(substr(MyAmount.sponsor_id,5,2))