Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Functions in a select
Message
De
25/08/2008 15:58:55
 
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
01341594
Message ID:
01341619
Vues:
14
Sorry Sergey, It's not clear what you meant. However, based on comments from the others this is what I have now.
select *, 00000000 as expended ;
from rta_elements ;
order by elementnum ;
into cursor crsElements readwrite

scan
replace expended with GetExpendituresBySubelement(elementnum)
endscan


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)
Any critique on this?

>Why din't you combine a query in UDF witrh your main select?
>
>
>>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
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform