1 2 Previous Next 15 Replies Latest reply on Feb 28, 2015 12:56 PM by philmodjunk

    Execute SQL function

    tays01s

      Title

      Execute SQL function

      Post

      A couple of queries:

      1. I'd got this calc field running:

      If(ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE (Route='G' or Route= 'I') AND \"_IOID\"=?";"";"";IO 2::__ID)="";0; ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE (Route='G' or Route= 'I') AND \"_IOID\"=?";"";"";IO 2::__ID) )

      I understand the need to use \"_IOID\" because of the underscore but I hadn't noticed the whole \"_IOID\"=?" has 3 " as opposed to the normal "XX" bracketing. Why is this?

      2. I later realised that I really need not (Route='G' or Route= 'I') but a different indicator of route [Feeds::Route="e"]. However, this means referring to 2 fields from different tables. I've attempted the syntax below, but not getting it right.

      If(ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE Route=? AND \"_IOID\"=?";"";"";Feeds::Route="e";IO 2::__ID)="";0;
      ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed WHERE Route=? AND \"_IOID\"=?";"";"";Feeds::Route="e";IO 2::__ID)
      )

      All I get is '0' when the sum definitely isn't 0.

        • 1. Re: Execute SQL function
          philmodjunk

          1) when you put the escape character \ in front of the double quote character, FileMaker replaces the two characters \" with just the " and treats it as a character, not the operator marking the end or start of a quoted string of text.

          2) Correct SQL syntax for a "fully qualified" field reference is TableName.FieldName instead of using TableName::FieldName. And you have to use a Join clause to define the relationship between FeedsUsed and Feeds before you can reference fields from the Feeds table in this query if you are going to refer to data from fields of both tables in the same SELECT statement.

          For more on FileMaker SQL syntax, see: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

          • 2. Re: Execute SQL function
            tays01s

            1. I'd mistakenly confused the terminal " of the SELECT with other "; hence my incorrect Q.

            2. Re. JOIN clauses, I can see how they'd work with a

            SELECT XXX FROM Table1 INNER JOIN Table2 ON T1.field = T2.field. However, I've got a T2.field="e" scenario. I have found an alternative solution to my problem by creating a calc field that brings the 'e' value onto Table1. I assume my problem really isn't best addressed using a JOIN since I don't have matching fields.

            • 3. Re: Execute SQL function
              philmodjunk

              If you don't have matching fields in your database, how can you define a calculation to bring in a value from table 2 into table 1?

              If you do have such matching fields defined in Manage | Database | relationships, you can use the same pair(s) of match fields in your SQL join clause so that a reference to a field in Table2 works as part of your WHERE clause.

              • 4. Re: Execute SQL function
                tays01s

                Sorry my intended Q was what do you do if there is >1 field you want to refer to in terms of syntax:

                ExecuteSQL("SELECT Field1 FROM Table1 WHERE Field2='X' AND \"Field_B\"=? AND \"Field_C\"=?";"";"";Table2::Field_B="Y" AND Table3::Field_C)

                I'm not sure of the syntax for the coloured characters.

                • 5. Re: Execute SQL function
                  philmodjunk

                  You would use Table2.Field_B, but only after including a join clause joining Table1 to Table2.

                  • 6. Re: Execute SQL function
                    tays01s

                    1. A query: The 'green' fields I've used '::' between Table::Field; I thought that was correct outside the "SELECT..... " code? Because in a previous post [http://forums.filemaker.com/posts/c7029d0d92] you'd mentioned using the syntax below, which works:

                    ExecuteSQL("SELECT SUM (amount) FROM FeedsUsed WHERE ( Route='G' or Route='I' ) AND ParentID = ?  ";"";"" ; Parent::ParentID)

                    2. Re. the current query, I have written:

                    ExecuteSQL("SELECT FieldX FROM TableR AS r INNER JOIN TableC AS c ON  r.std=c.std_en AND r.age_threshold>c.age AND r.sex=c.sex";"";"")

                    The calculation field returns a value (only if 'text', not as 'number') for r.std=c.std_en or r.sex=c.sex individually not together and not at all for r.age_threshold>c.age.

                    Is there a way to join >1 matching pair of fields and can you specify that a field in 1 table has to be more or less that the value in another table so that when (in this case) all 3 matches are made, I get the 1 value I am after?

                    • 7. Re: Execute SQL function
                      philmodjunk

                      1) Sorry, I misread your last post. I what I saw looked like part of the WHERE clause and I didn't see the details that make this an optional parameter that passes a value to the query.

                      What you have uses field reference notation correctly, but doesn't necessarily work like you would expect and you have two references to optional parameters but only one listed.

                      Table2::Field_B="Y" AND Table3::Field_C

                      Evaluates as a single value of either 1 (True) or 0 (False) and this value is put in place of the first ? when the SQL evaluates. But you don't then have any value for the second ? in your expression.

                      2) can you try that again? "Only if text not as number" means? and what exactly are you trying to do here with this query? Your join is similar to setting up 3 pairs of match fields in a relationship in FIlemaker's relationship graph though the "INNER" keyword will change results to limit them to only records from R that have at least one matching record in C that matches on all three pairs of match field expressions.

                      • 8. Re: Execute SQL function
                        tays01s

                        1. So if I correct to this:

                        ExecuteSQL("SELECT Field1 FROM Table1 WHERE Field2='X' AND\"Field_B\"=? AND \"Field_C\"=?";"";"";Table2::Field_B="Y" AND Table3::Field_C="Z")

                        it should give me the value from Field1 providing the X, Y, Z criteria are met? And on this point, if I'd wanted to use < or > etc in place of '=' do the normal math rules apply?

                        2. In this alternative attempt to get to the same answer:

                        a) I found that when I tried putting only r.std=c.std_en or r.sex=c.sex in the equation, they would pull up the first value based on that criteria, but only if I made the calculation field result text; if it was 'number' I got "?".

                        b) Your comment: "Your join is similar to setting up 3 pairs of match fields in a relationship in FIlemaker's relationship graph though the "INNER" keyword will change results to limit them to only records from R that have at least one matching record in C that matches on all three pairs of match field expressions." is exactly what I was trying to achieve. However, if I try to use >1 of my matches, it fails and in the case of "r.age_threshold>c.age" it fails even on it's own. I'd wondered if '>' functions were permitted. However, it's obvious I'm getting something wrong on 2-3 levels with this 2nd equation.

                        • 9. Re: Execute SQL function
                          philmodjunk

                          Nope.

                          You are still missing an optonal script parameter and this is not likely to evaluate like you expect. The entire espression evaluates down to a single number of 1 or 0 that is then inserted into your expression. the WHERE clause then becomes:

                          "Field_B\"=1 AND \"Field_C\"=?

                          or it becomes:

                          "Field_B\"=0 AND \"Field_C\"=?

                          Doesn't seem likely that you are using this expression to determine if the value in Field_B is a 1 or a 0 and it still leaves no value to put in place of the second question mark.

                          • 10. Re: Execute SQL function
                            tays01s

                            You're right I'm not fully understanding this. I need to find the value of Field X from Table 2:

                            - T1.age < T2.age

                            - T1.sex=T2.sex

                            - T1.std=T2.std

                            How would I do this using Execute SQL function?

                            • 11. Re: Execute SQL function
                              philmodjunk

                              And what result do you want if there is more than one record in Table 2 that matches on all three of these fields?

                              What you are describing could be set up this way:

                              ExecuteSQL ( "

                              Select Field_X from T2 WHERE age = ? and sex = ? and std = ? "

                              ; "" ; "" ; T1::age ; T1::sex ; T1::Std )

                              This assumes that your field is defined in  the T1 table.

                              • 12. Re: Execute SQL function
                                tays01s

                                The suggested equation almost works, in that if I drop the 'age' criterion, I get a first record that meets the 'sex' and 'std' criteria. However, for 'age' to work, the 'actual age', ie. the one I am looking up, needs to be '<' the age in the table with the value I am trying to find. The latter table has records with age categories: 3y, 10y, 18y etc. So for someone aged 8y, I want the value in the 10y age category. I could convert the 'actual age' to an age category in a new field, but each 'std' has different categories.

                                I have tried using 'age < ?' but it doesn't work; I'm not sure if '< or >' syntax is permitted.

                                Lastly, for a given age [category], sex and std, there is only 1 value that can be listed. It's how to get it that eluding me!!

                                • 13. Re: Execute SQL function
                                  philmodjunk

                                  < and > symbols are supported. But if the field type is text, the comparisons won't evaluate like you want. I suggest changing the field type of the field to number.

                                  Example of a text comparison that doesn't produce the expected result:

                                  "2" > "10"

                                   Returns true because the first character of "2" is greater than "1" the first character of "10".  But if you use number data types instead of quoted text, the result is false as you would expect.

                                  • 14. Re: Execute SQL function
                                    tays01s

                                    There was a problem with one of the 'age' fields, now fixed and I can get a correct result in the Calc field using Execute SQL. However, I hate to admit this but........I was struggling so much with the syntax, I didn't question why the calc field needed to be text to work......when actually what it need was it's decimal points limited to return a number......so dumb. Apologies!!

                                    1 2 Previous Next