AnsweredAssumed Answered

Nested ExecuteSQL?

Question asked by CP42Kx07 on Aug 26, 2014
Latest reply on Aug 29, 2014 by beverly

Following some interesting ideas in response to another SQL-related question I am attempting to use SQL to create faster find steps in certain scripts.


The aim is to find a set of records that match three criteria (Tx Category = "Asset", Tx Date <= TX__Txns::Tx g_Filter Date, SUM (Tx aQty) > 0) and for the result to be a list containing one field only (Tx ID).


The first two criteria are straightforward. The third one has been set-up as an aggregate (to avoid referencing a FM unstored calc field) when grouped on two other fields (As ID and Pf ID) that have been combined solely for this purpose (Tx AsIDPfID).


Is there a way to combine these two fields for the purposes of GROUP BY in the SQL syntax itself rather than in a stored calc field in FM?


The result (successful) is a list of matching records (Tx AsIDPfID) but of course I really need a list containing a different field (Tx ID) for each record.


Do I need to nest SELECT expressions or is there a far simpler way? It seems that I cannot just add Tx ID to the SELECT line without also adding it to the GROUP BY one which probably reduces execution speed (with no group / sort benefit as Tx ID is a unique key field) and still leaves me having to further process the result to remove the unwanted field...









Let (

[ $query = "


FROM "Tx__Txns" tx

WHERE tx."Tx Category" = ? AND tx."Tx Date" <= ?

GROUP BY tx."Tx AsIDPfID" HAVING SUM ( tx."Tx aQty" ) > 0"

; $result =

ExecuteSQL (

$query ;

", " ;

"¶" ;

"Asset" ;

TX__Txns::Tx g_Filter Date )

] ;