>>>This is exactly what I gave you in one of my messages as well.
>>>
>>>Re: Functions in a select Thread #1341594 Message #1341631
>>
>>Almost but not quite. Cetin's works, your doesn't. Note the difference in the last ON clause.
>>
>>Thanks for your help anyway. I still learned something from the discussion.
>
>I see no difference except for CAST as Integer. That may be a good point, actually.
>
>cast(val(substr(sponsor_id,5,2)) as int)

Here is your code

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

Here is Cetin's

select rta_elements.*, cast(nvl(tmp.amount,0) as int)/1000 as expended ;
  from rta_elements ;
  left join ;
  (select cast(val(substr(sponsor_id,5,2)) as int) as sponsor_id, sum(amount) as amount ;
     from tip ;
         inner join ledger on ledger.tip_id = tip.tip_id and source="RTA" ;
         group by 1) tmp ;
  on tmp.sponsor_id = rta_elements.elementnum 

Yours says: ON R.ElementNum = myAmount.GroupID
his on tmp.sponsor_id = rta_elements.elementnum

note that you reference the derived table 'MyAmount' inside the select which creates it. This throws an error that says field MyAmount does not exist..