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

I think other than that they are the same. One of you groups by 1 the other by 2. I'm not sure if there's a difference. The GROUP BY clause still confuses me.

The division by 1000 is mine not Cetin's. All the numbers in the report are presented in (000's). The expenses however are to the penny in the table so they need to be truncated. Maybe not the best way but it seems to work.