1 2 3 Previous Next 41 Replies Latest reply on Aug 4, 2015 3:22 AM by tays01s

    Execute SQL: Searching

    tays01s

      Title

      Execute SQL: Searching

      Post

      I have a simple query: ExecuteSQL("SELECT Na FROM Reqs_EN WHERE Age_threshold>?;Sex=?;Std=?";"";"";IO 2::Age;IO 2::Sex;IO 2::Std_EN)

      The above is evaluated from TO 'IO 2'.

      Na and Age fields are 'numbers', Sex and Std fields are 'text'. The IO 2::calc field is 'number' and the layout set to decimal though some answers could be text.

      Unfortunately the answer is always '?' unless I reduce criteria to 'Sex', in which case the calc returns '57', a value that has nothing to do with the Na field I wanted searched.

      Anything I should search for?

        • 1. Re: Execute SQL: Searching
          philmodjunk

          don't use the semi-colons in your WHERE clause. Separate them with AND, or  OR--depending on the result that you want produced.

          • 2. Re: Execute SQL: Searching
            tays01s

            Ah yes. Sadly that was a recent mistake as I was typing, but it doesn't work even when corrected to:

            ExecuteSQL("SELECT Na FROM Reqs_EN WHERE Age_threshold>? AND Sex=? AND Std=?";"";"";IO 2::Age;IO 2::Sex;IO 2::Std_EN)

            • 3. Re: Execute SQL: Searching
              philmodjunk

              Do you have FileMaker Advanced? There's a custom function you can use with the data viewer to get actual error messages back instead of the ? result. Let me know if you have Advanced and I'll share the function.

              I'd check to see if age_threshold is really the correct name for a field in Reqs_EN. you an also try enclosing the table and field names in double quotes though nothing here strikes me as needing that added detail.  (WHERE \"Age_threshold\" >?)

              • 4. Re: Execute SQL: Searching
                tays01s

                You must be telepathic! The 'Age' was a problem, but because I'd abbreviated threshold to 'age_thresh'.

                So it now gives a list of values, all Age thresholds above Age. I need it to grab the value of only the 1st Age threshold that is above Age.

                FM advanced: Yes, I have it so would love to have that function. It may help reduce the number of times I display ignorance on the Forum!!!!

                • 5. Re: Execute SQL: Searching
                  philmodjunk

                  I didn't need telepathy I just looked for a way that you could get a syntax error when SELECT with just Sex used in the WHERE clause did not produce an error:

                  THis is one of the most unusual CF's that I've seen but it exploits a bit of serendipity on the part of it's creator to expose a result that we really need to see without the use of this custom function:

                  If (

                  //the sql call results in an error, return empty so the error will be returned
                  _executeSQL = "?" ; "" ;

                  //the sql call is executed correctly, just return the result
                  _executeSQL
                  )

                  // ===================================
                  /*

                      This function is published on FileMaker Custom Functions
                      to check for updates and provide feedback and bug reports
                      please visit http://www.fmfunctions.com/fid/335

                      Prototype: sql.debug( _executeSQL )
                      Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
                      Last updated: 28 July 2012
                      Version: 2.2

                  */
                  // ===================================

                  To use this CF, open the Data viewer and create a watch expression like this:

                  SQL.Debug (
                  ExecuteSQL ( "Your Select query goes here....
                                    )

                  You then click the monitory button then re-open the watch expression for editing and look at the evaluate box for an error message. Sometimes you just get "syntax" and you are no better off, but sometimes you get just the clue that you need to fix your query.

                  • 6. Re: Execute SQL: Searching
                    philmodjunk

                    I need it to grab the value of only the 1st Age threshold that is above Age.

                    What is your definition of "first"?

                    You may need to include an "order By" clause to make sure that the "first" value returned by the query is the "first" value that you want to see.

                    You can enclose the executeSQL function call inside a Leftvalues ( ExecuteSQL.... ; 1 ) or GetValue ( ExecuteSQL ... ; 1 ) function call provided that you use ¶ as your record separator parameter.

                    Or you can use the "FETCH FIRST" clause to return only the first row of data as documented here: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                    • 7. Re: Execute SQL: Searching
                      tays01s

                      Leftvalues worked nicely thanks. However, I'd be interested to know why FETCH FIRST did not:

                      ExecuteSQL("SELECT Na FROM Reqs_EN WHERE Age_thresh>? AND Sex=? AND Std=? ORDER BY Age_thresh FETCH FIRST";"";"";IO 2::Age;IO 2::Sex;IO 2::Std_EN)

                      • 8. Re: Execute SQL: Searching
                        philmodjunk

                        What happens if you use FETCH FIRST ROW ONLY ?

                        • 9. Re: Execute SQL: Searching
                          tays01s

                          I thought I'd previously done that, obviously not. Thanks, very helpful functionality.

                          • 10. Re: Execute SQL: Searching
                            tays01s

                            Re. the Custom Function you mentioned, can I just check:

                            1. I create an SQL.debug 'New function ( ) =' If ( _executeSQL = "?" ; "" ; _executeSQL ) and the _executeSQL is the function parameter.

                            2. Then in Data Viewer I create a new expression to watch. I don't get the connection to the custom function or how the suggested expression window text will work:

                            SQL.Debug (
                            ExecuteSQL ( "Your Select query goes here....
                                              )

                            NB Isn't there a ) missing?

                             

                            • 11. Re: Execute SQL: Searching
                              philmodjunk

                              It's definitely a "smoke and mirrors" function and not one that I created as the comment section indicates. I'm just repeating the directions I was given in how to use it. Don't know if you can find a new way to get the message text out.

                              Yes there a ) missing as is a lot of other detail as shown by the ellipsis in the second row of text.

                              To spell out an example in more detail:

                              SQL.Debug (
                              ExecuteSQL("SELECT Na FROM Reqs_EN WHERE Age_threshold>? AND Sex=? AND Std=?";"";"";IO 2::Age;IO 2::Sex;IO 2::Std_EN)
                              )

                              And you have to click "Modify", then re-open the watch expression and check the evaluate box to see any error text.

                              • 12. Re: Execute SQL: Searching
                                tays01s

                                I appear to have got the SQL.debug working, thanks.

                                1 query it's can't asnwer though. In the calc below, the ESQL works correctly. However, 'na' is sometimes an integer (eg. 5) but sometimes needs to be Evaluated (eg. Calc::kg*2). I've made both the 'na' and calc fields 'text', and in layout mode the result field is 'As entered'. However, for 'na' values that are in effect 'nested calcs', it returns '0'.

                                Have erred in matching Evaluate with ESQL?

                                Evaluate (
                                ExecuteSQL("SELECT na FROM Reqs WHERE Age_thresh>? AND Std=? ORDER BY Age_thresh FETCH FIRST 1 ROW ONLY";"";"";Calc::Age;Calc::Std_PN)
                                )

                                • 13. Re: Execute SQL: Searching
                                  philmodjunk

                                  I would suspect that something is preventing the value returned by eSQL from producing an expression that evaluates correctly. I'd carefully check the data returned by the query each time that you get an unexpected result. Note that if calc::Kg is zero or empty, then Evaluate ("calc::kg*2" ) would correctly return the value zero.

                                  • 14. Re: Execute SQL: Searching
                                    tays01s

                                    From what I've found, there seems a systematic returning of: a) the correct number, if the 'na' field in 'Reqs' table only contains an an integer but b) a number '1' from a text such as Calc::kg*1; it competely ignores the calc.

                                    The above appears to happen becuase despite my source and calcs fields being text fields, that Evaluate doesn't get presented with text by the ESQL; I've checked ESQL, and it only presents the number, not the text.

                                    I assume an ESQL calc can be nested inside an 'Evaluate'?

                                    1 2 3 Previous Next