Let ( [
FDF = GetAsDate ( $FilterDateFinal ) ; // Added the GetAsDate as a precaution
SQL = "SELECT
\"Tx Category\" = ? and
\"Tx Date\" <= ? and
\"Tx Hg As Pf Qt\" <> ?" ;
Exe = ExecuteSQL ( SQL ; Char ( 9 ) ; ¶ ; "Asset" ; FDF ; 0 )
// Note that if you want the result to behave as a number and not text, you should add: GetAsNumber ( Exe )
Perhaps the "date" becomes type "text" when used this way?
How are you creating the variable? And the single quote is important. Did you try this?
$FilterDateFinal = "'" & GetAsText( myDatefield ) & "'" // that's double-quotesingle-quotedouble-quote before and after the date
Or, if the Tx Date column is date type, value to compare should be date, so try
FilterDateFinal = GetAsDate ( $FilterDateFinal ) ;
Thanks. I have tried numerous combinations of GetAsText / GetAsDate and variations on the single / double quotes but to no avail. There is no problem running the script using the original global date field as the parameter, it just doesn't work with the local variable based on that field.
In any event the hoped for speed improvements have not materialized so I may stick with the original, simple, Perform Find step rather than over-complicate life.
As I have indicated in my reply to Beverly above, no luck so far but your suggestion was worth a try.
I have been unable to make your version work despite trying quite a few variations and so have resorted to a basic version that uses the original field on which the variable is based:
"SELECT \"Tx ID\"
WHERE \"Tx Category\" = ? AND \"Tx Date\" <= ? AND \"Tx Hg As Pf Qty\" > ?";
TX__Txns::Tx g_Filter Date;
I like the greater elegance of your expression though.
As a supplementary to the original question, can anyone give me some idea of expected speeds for find scripts based on 1) a standard Perform Find script 2) an ExecuteSQL based script.
the find criteria: = text field AND <= date field AND <> unstored calculation field
the sort order: 3 unstored calculation fields
current total records: 3,671
found records: 429
Both types of find & sort script take c. 9 secs, most of which time appears to be used for the find.
Are ExecuteSQL type finds expected to take the same time as Perform Find ones? I was rather hoping that they might be somewhat faster (although why I'm not sure!). Perhaps the problem is with the unstored calculation field? Unfortunately this is a calculation based on a TO that I cannot avoid.
Are there any known optimisation tricks for SQL finds?
edit: I suspect that the only way for me to get to grips with this will be to really spend some serious time learning more about SQL.
1 of 1 people found this helpful
There's unlikely to a speed difference between a well constructed scripted find and an ExecuteSQL(). Both rely on the same indexes, etc.
However, there's usually ways to improve the performance of either.
For example, you can do a scripted find on the indexed fields, then use Constrain Found Set on the unstored calc.
Likewise, I think there's a way to nest ExecuteSQL() statements to do something similar, though I'm not that good at ExecuteSQL() so I can't give you direct advice there.
Also, you may be able to change the unstored calc to a stored on and have it updated by script whenever its value changes.
I like the sound of the Constrain Found Set which at first glance makes sense.
I am considering diving into SQL as I suspect that there may be a way to create a nested expression that will bypass the role of the unstored calc.
Unfortunately the script / stored calc or number field would probably be impractical for my purposes.
All good suggestions though.
Using variable in param should be work, but your point is changed...
SQL in FM doesn't support SELECT in FROM, so how about using custom function that filter list.
"SELECT \"Tx Hg As Pf Qty\", \"Tx ID\"
WHERE \"Tx Category\" = ? AND \"Tx Date\" <= ?
and apply removing filter of value biginning with "0"&(column separator)
This looks like an intriguing idea although as yet my SQL knowledge is insufficient to test it correctly.
I'll certainly try it once I'm up to speed with SQL.
I suspect that the solution will be to try and avoid accessing the unstored calculation field (Tx Hg As Pf Qty) at all, but this will probably involve using SUM in SQL which I believe is slow anyway...
Thanks for the suggestions.