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...
[ $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 =
", " ;
TX__Txns::Tx g_Filter Date )