>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?
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 elementnumCetin