>>This function
>>

>>function GetExpendituresBySubElement
>>lparameters lnSubElement
>>local lnOldWorkArea, lnRetVal
>>dimension lnRetval(1)
>>lnOldWorkArea = select(0)
>>
>>select  sum(amount) ;
>>from tip ;
>>inner join ledger on ledger.tip_id = tip.tip_id and source="RTA" ;
>>where val(substr(sponsor_id,5,2)) = lnSubelement;
>>into Array lnRetVal
>>
>>select(lnOldWorkArea)
>>return iif(isnull(lnRetVal),0,lnRetVal)
>>

>>seems to work OK when called with a single value like this
>>

>>? GetExpendituresBySubelement(2)
>>

>>But when embedded in another select like this
>>

>>select *, GetExpendituresBySubelement(elementnum) as expended ;
>>from rta_elements ;
>>order by elementnum
>>

>>I get only 0's and *'s. Can someone please explain?
>>
>>Thanks
>
>You could prevent that using a cast. However calling such a function wouldn't be feasible. Instead youı might do somethging like:
>
>

select rta_elements.*, cast(nvl(tmp.amount,0) as y) 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 ;
>  order by elementnum
>

Cetin

Thanks Cetin. That one works!