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

 

Thanks

 

 

CP

 

 

 

Let (

[ $query = "

SELECT tx."Tx AsIDPfID"

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 )

] ;

$result

)

Outcomes