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

I see, that was just a typo, of course. BTW, why does he divide by 1000? Other than that I still don't see a difference.