11 Replies Latest reply on Aug 26, 2014 12:52 AM by CP42Kx07

    Using Variables as ExecuteSQL Parameters

    CP42Kx07

      I am just starting to play around with the ExecuteSQL script step.

       

      How does one incorporate a variable (local or global) as a parameter?

       

      I am able to make the search work using a field:

       

      ExecuteSQL(

      "SELECT "Tx ID"

      FROM "Tx__Txns"

      WHERE "Tx Category" = 'Asset' AND "Tx Date" <= ? AND "Tx Hg As Pf Qty" > 0";

      "";

      ¶;

      TX__Txns::Tx g_Filter Date)

       

      but am unable to do so using Let() and a local variable (type: date) from the calling script:

       

      Let (

      FilterDateFinal = $FilterDateFinal ;

      ExecuteSQL(

      "SELECT "Tx ID"

      FROM "Tx__Txns"

      WHERE "Tx Category" = 'Asset' AND "Tx Date" <= ? AND "Tx Hg As Pf Qty" <> 0";

      "";

      ¶;

      FilterDateFinal ) )

       

      I have tried single quote marks around the variable (FilterDateFinal = '$FilterDateFinal') to no avail.

       

      Alternatively, is there a way to use the variable directly in the WHERE request ("Tx Date" <= $FilterDateFinal) or similar?

       

      Any ideas would be much appreciated. Thanks

       

       

      CP

        • 1. Re: Using Variables as ExecuteSQL Parameters
          taylorsharpe

          Let ( [

           

          FDF = GetAsDate ( $FilterDateFinal ) ;   //  Added the GetAsDate as a precaution

          SQL = "SELECT

                              \"Tx ID\"

                      FROM

                              Tx__Txns

                      WHERE

                              \"Tx Category\" = ? and

                              \"Tx Date\" <= ? and

                              \"Tx Hg As Pf Qt\" <> ?" ;

          Exe = ExecuteSQL ( SQL ; Char ( 9 ) ; ¶ ; "Asset" ; FDF ; 0 )

           

          ] ;

           

          Exe

           

          )

           

           

           

          //  Note that if you want the result to behave as a number and not text, you should add:  GetAsNumber ( Exe )

          • 2. Re: Using Variables as ExecuteSQL Parameters
            beverly

            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

             

            Beverly

            • 3. Re: Using Variables as ExecuteSQL Parameters
              user19752

              Or, if the Tx Date column is date type, value to compare should be date, so try

               

              FilterDateFinal = GetAsDate ( $FilterDateFinal ) ;

              • 4. Re: Using Variables as ExecuteSQL Parameters
                CP42Kx07

                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.

                • 5. Re: Using Variables as ExecuteSQL Parameters
                  CP42Kx07

                  As I have indicated in my reply to Beverly above, no luck so far but your suggestion was worth a try.

                  • 6. Re: Using Variables as ExecuteSQL Parameters
                    CP42Kx07

                    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:

                     

                    ExecuteSQL(

                     

                     

                    "SELECT \"Tx ID\"

                    FROM \"Tx__Txns\"

                    WHERE \"Tx Category\" = ? AND \"Tx Date\" <= ? AND \"Tx Hg As Pf Qty\" > ?";

                     

                     

                    "";

                    ¶;

                     

                     

                    "Asset";

                    TX__Txns::Tx g_Filter Date;

                    0)

                     

                    I like the greater elegance of your expression though.

                    • 7. Re: Using Variables as ExecuteSQL Parameters
                      CP42Kx07

                      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.

                      • 8. Re: Using Variables as ExecuteSQL Parameters
                        DavidJondreau

                        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.

                        1 of 1 people found this helpful
                        • 9. Re: Using Variables as ExecuteSQL Parameters
                          CP42Kx07

                          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.

                          • 10. Re: Using Variables as ExecuteSQL Parameters
                            user19752

                            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\"

                            FROM \"Tx__Txns\"

                            WHERE \"Tx Category\" = ? AND \"Tx Date\" <= ?

                             

                            and apply removing filter of value biginning with "0"&(column separator)

                            • 11. Re: Using Variables as ExecuteSQL Parameters
                              CP42Kx07

                              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.