10 Replies Latest reply on Aug 16, 2016 7:11 PM by Nick Austen

    ExecuteSQL - using a variable as target select from field

    Nick Austen

      Hi all

      I've been following Soliant Consulting's dynamic charting tutorial (http://www.soliantconsulting.com/blog/2014/05/dynamic-charting-filemaker)

      Very useful technique!

       

      My question is - can i use a variable as in ExecuteSQL in the following way to dynamically choose which column to select from? (using the same structure as in the posting above here):

       

      Let ([_Row = 1;

      _Item = GetValue (GLOBALS::g_ModelsToShow ; _Row );

      $_Measure = GLOBALS::g_MeasureToShow

      ];

      ExecuteSQL("

      SELECT $_Measure

      FROM Forecast_Data

      WHERE ModelName = ?

      ORDER BY MonthNumber"

      ;"";"";_Item)

      )

        • 1. Re: ExecuteSQL - using a variable as target select from field
          planteg

          Hi Nick,

           

          the SELECT statement would need to be changed to:

           

          "SELECT " &  $_Measure &

          "FROM Forecast_Data

          WHERE ModelName = ?

          ORDER BY MonthNumber"

           

          so the contents of $_Measure is used to determine the column to query. Better, replace $_Measure by ? and pass the column name as a parameter.

           

          If you want to insure your query is not broken by changing the name of the column for example, see  https://filemakerhacks.com/2012/05/13/fm-12-executesql-robust-coding-part-1/

          1 of 1 people found this helpful
          • 2. Re: ExecuteSQL - using a variable as target select from field
            jbrown

            Hey Nick.

            That was my blog post you mentioned. Thanks for the feedback. I was just about to respond to your comment.

            I 2nd the idea of using the robust coding of ExecuteSQL, as mentioned above. That is another thing I can't live without.

             

            That's a great use of the variable. You just have to be sure your variable contains the exact field name. If you use the robust coding, you'd have to dynamically choose the field name, something like

             

             

            Let )[

             

            $_Measure =

            Case (

            GLOBALS::g_MeasureToShow = "Monthly" ; GetFieldName (YourTable::MonthlyField);

            GLOBALS::g_MeasureToShow = "Production" ; GetFieldName (YourTable::Production);

             

            Something like that.

             

            I'd be happy to work through it with you if you'd like. Let me know.

             

            EDIT:

            Actually my above thoughts won't work. To use the robust coding technique, I think you'd have to build the SELECT statement depending on what the value of $_Measure is.

            (And By the way, you don't have to use the $ in the LET statement)

            2 of 2 people found this helpful
            • 3. Re: ExecuteSQL - using a variable as target select from field
              Nick Austen

              planteg and jbrown

              Thanks guys that's awesome. I'm off on something else for now but I think you've given me exactly what I was looking for.

              Jeremy - that dynamic charting stuff is great. I am experimenting for now but have a couple of clients who I think will love it - dashboards and quick comparisons etc. I'm also looking at building on your approach to present year on year / MoM charts. I will have a go at it myself and thanks for the offer of assistance!

               

              Thanks both for your quick responses!

              Nick

              • 4. Re: ExecuteSQL - using a variable as target select from field
                Nick Austen

                Hi jbrown and planteg

                I have tried numerous approaches based on your thoughts above. Different variations to try and parameterise the field name:

                 

                "ExecuteSQL("

                SELECT" & _Measure &

                "FROM Forecast_Data ....

                 

                I tried:

                1. Setting _Measure in the start of the Let

                2. Setting $_Measure in the same place statically (i.e. "Production")

                3. Using GetFieldName(GLOBALS::g_MeasureToShow)

                4. Using "Select ?... then passing the variable at the end (both as static text of the field name or GetFieldName

                 

                None seem to work though. I can obviously use a CASE statement in the Y Data calculation but was hoping to make it as generic and flexible as possible

                 

                Any other ideas?

                 

                Thanks again!

                • 5. Re: ExecuteSQL - using a variable as target select from field
                  beverly

                  One thing to remember:

                  the SELECT statement is a quoted string, composed of text, field references and concatenated.

                   

                  I believe what jbrown  was saying

                   

                  _monthlyQuery = " SELECT " & GetFieldName (YourTable::MonthlyField)  & ....

                  _productionQuery = " SELECT " & GetFieldName (YourTable::Production) & ...

                   

                  Then in your "query":

                   

                  _query = Case ( GLOBALS::g_MeasureToShow = "Monthly" ; _monthlyQuery

                       ; GLOBALS::g_MeasureToShow = "Production" ; _productionQuery )

                   

                  ExecuteSQL ( _query ; "" ; "" )

                   

                  You are dynamically building the query (text string, remember?) and calling as needed with one ExecuteSQL()!

                  of course, not tested, but you get the idea.

                   

                  beverly

                  • 6. Re: ExecuteSQL - using a variable as target select from field
                    Nick Austen

                    Thanks beverly !

                    (insert sound of penny dropping! )

                    I had to try a few options to get it to work, the final piece of the puzzle was moving parameters to the ExecuteSQL (...) line.

                     

                    For the record the following works! Thanks again to all planted and beverly and jbrown

                     

                    Let ([

                    _Row = 1;

                    _Item = GetValue (GLOBALS::g_ModelsToShow ; _Row );

                     

                    _retailQuery =

                    "SELECT hdk_DealerRetail FROM Forecast_Data

                    WHERE ModelName = ?

                    AND MyMonth >= ? AND MyMonth <= ?

                    ORDER BY MonthNumber

                    ";

                     

                    _productionQuery =

                    "SELECT hdk_Production

                    FROM Forecast_Data WHERE ModelName = ?

                    AND MyMonth >= ? AND MyMonth <= ? ORDER BY MonthNumber

                    "

                    ;

                     

                    _query = Case (

                    GLOBALS::g_Measure = "hdk_DealerRetail"; _retailQuery;

                    GLOBALS::g_Measure = "hdk_Production"; _ProductionQuery

                    )

                    ];

                     

                     

                    ExecuteSQL ( _query ; "" ; "";

                    _Item;

                    Filters::Start_Month;

                    Filters::End_Month)

                    )

                    • 7. Re: ExecuteSQL - using a variable as target select from field
                      beverly

                      absolutely, get into the habit of using the parameters! FM eSQL is smart enough to know if (unquoted) value is a number and quoted is text. Thus it substitutes into the parameter placeholder the proper SQL quoting: no quotes on numbers and single quotes on text.

                      That being said:

                      GetAsNumber ( "1" ) is the same as 1 - a number. So when using variables, it may or may not be necessary to cast GetAsNumber, GetAsText, GetAsDate, etc. to ensure the proper quoting is applied. Some will say this is NOT required. So I added the "may or may not".

                       

                      When in doubt, I leave no confusion!

                      beverly

                      2 of 2 people found this helpful
                      • 8. Re: ExecuteSQL - using a variable as target select from field
                        Nick Austen

                        Hi beverly

                        Definitely agree on using parameters. What I meant was I was trying to put them in the _query string, as opposed to in the ExecuteSQL () part.

                         

                        Basic error I guess but once I resolved that it works like a charm

                        • 9. Re: ExecuteSQL - using a variable as target select from field
                          beverly

                          you used variables as the parameters to be 'substituted' and that was correct. So you can make them "dynamic" as well (to a point, of course).  You can't have one query with two "?" and another with three "?" in the case. But yes, they can be "hard-coded" with variables (defined earlier):

                           

                          ; "" ; ""

                          ; _param1 ; _param2 ; _param3 )

                           

                          but as I said, pushing variables may not be passing correctly & nof quoting correctly, so I would:

                          ; "" ; ""

                          ; GetAsNumber ( _param1 ) ; GetAsDate ( _param2 ) ; GetAsText ( _param3 ) )

                          even it is seems correct when these parameters where set earlier in the Let() statements. It's extra 'steps', but adds clarity!!

                          1 of 1 people found this helpful
                          • 10. Re: ExecuteSQL - using a variable as target select from field
                            Nick Austen

                            Thanks once again beverly

                            I've added this to my solution and will keep it in mind going forward. Incidentally the concept helped me flush out a semi-related issue - I was getting a 'type mismatch' on converting a product ID (number) to product name (text) which meant unexpected results.