1 2 Previous Next 16 Replies Latest reply on Jan 31, 2014 4:37 AM by mikebeargie

    ExecuteSQL advanced query limits?

    mikebeargie

      I am getting a ? from a statement I thought would work, but apparently does not.

       

      I have a "RACE_DATA" table that holds historical results from elections by county. So the data looks something like this:

       

      a__kp_idKeya_kf_raceIdKeya_kf_countyIdKeycandidateCount
      11505
      215010
      31515
      42505
      525010
      62515

       

      I am looking to get the sum of candidateCount, from the MOST RECENT (based on primary key) countyIdKey record, based on race::a__kp_idKey entered as the variable in the statement. So my query should return 15 on the above table (assuming raceIdKey = 1), from serial numbers 2 and 3.

       

      My query (as a calc field in the main RACES table) I've tried is as follows:

      ExecuteSQL (

      "

      SELECT sum(candidateCount)

      FROM RACE_DATA

      QUALIFY

      1 = ROW_NUMBER() OVER (PARTITION BY a_kf_countyIdKey ORDER BY a__kp_idKey DESC);

      WHERE a_kf_raceIdKey = ?

      "

      ; ""; "" ; a__kp_idKey)

       

       

      I am assuming that the Qualify statement is not valid for filemaker's ExecuteSQL.

       

      I may be thinking too far into this, but can someone see an obvious solution to why this isn't rendering? I'm doing trial and error with other methods in the meantime.

        • 1. Re: ExecuteSQL advanced query limits?
          BruceHerbach

          Mike,

           

          I believe that the data table and the field for the where statement have to be fully qualified field names (Table::Field ).  To help me develop these I have been using SeedCode's  SQLExplorer.

          Anyway,  I think your query should look something like this...

           

          ExecuteSQL (

          "

          SELECT sum(a.candidateCount)

          FROM \"RACE_DATA\" a

          QUALIFY

             1 = ROW_NUMBER() OVER (PARTITION BY a.a_kf_countyIdKey ORDER BY a.a__kp_idKey DESC);

          WHERE a.\"a_kf_raceIdKey\" = ?

          "

          ; ""; "" ; RACE_DATA::a__kp_idKey)

           

           

           

           

           

          Another resource is The missing FM 12 ExecuteSQL Reference by Beverly Voth.  You can get this here:  http://www.filemakerhacks.com/?p=6406

           

          I'm still working through this and new to SQL.  But have found both of these to be very helpful.  See the section on Case statements,  it may help you solve the Qualify issue Nick points out.

           

           

          HTH

          Bruce

           

          Message was edited by: Bruce Herbach

          • 2. Re: ExecuteSQL advanced query limits?
            nickchapin

            Hi Mike:

             

            You're correct in that, AFAIK, the Qualify statement is not supported in FMP. Nor is the Partition by statement. You'll need to re-write to get rid of those bits.

             

            Bruce is correct that it's best practice to fully qualify your fields, but it's not a necessity if querying only one table.

             

            Finally, you may want to find and download the SQLdebug custom function; it helps by letting the developer know what part of the query is having problems.

             

             

            Nick

            • 3. Re: ExecuteSQL advanced query limits?
              mikebeargie

              Thanks Nick and Bruce, I actually found this guide to be helpful:

               

              http://www.filemakerhacks.com/?p=4924

               

              It contains a list of the reserved SQL words that filemaker uses, which can point me in the direction of where I need to go.

               

              As an FYI, even though "FIRST" is a reserved word, I could not get it to work in playing around, even with the fully qualified field names.

               

              ExecuteSQL ( "

              SELECT FIRST

              FROM RACE_DATA r

              WHERE r.a_kf_raceIdKey = ?

              ORDER BY r.a__kp_idKey DESC

              GROUP BY r.a_kf_countyIdKey

              " ; "" ; "" ; RACE::a__kp_idKey )

              • 4. Re: ExecuteSQL advanced query limits?
                gwinzeler

                Did you try Group by ....   then  Order by...    ?

                • 5. Re: ExecuteSQL advanced query limits?
                  beverly

                  Yes, ORDER BY needs to be the last clause in the SELECT statement.

                   

                  Beverly

                  • 6. Re: ExecuteSQL advanced query limits?
                    mikebeargie

                    I've tried it without ORDER BY at all and am still not able to get anything returned.

                     

                    At this point I am imagining SELECT FIRST is not supported even though the name is reserved.

                    • 7. Re: ExecuteSQL advanced query limits?
                      nickchapin

                      Correct; First is not supported in FM's SQL.

                       

                      The list or reserved words is for SQL; it has no bearing on whether it's supported in FileMaker.

                       

                       

                      Nick

                      • 8. Re: ExecuteSQL advanced query limits?
                        mikebeargie

                        So is anyone aware of an ExequteSQL statement that will query, group by, order by and return the first row of each group? Everything I read up on returning the first row of a group in SQL is not supported by filemaker (IE TOP, FIRST, QUALIFY, etc..)

                         

                        I would figure this would be easy, but it's not?

                        • 9. Re: ExecuteSQL advanced query limits?
                          beverly

                          In my tests, I found that FIRST(), LAST(), TOP, LIMIT, ROWNUM (ways to constrain the 'found set') do NOT work with ExecuteSQL function.

                           

                          If you can narrow your found records, you can use GetValue ( sql_result; 1 ), if the row-delimiter is the return- char(13)

                           

                          Beverly

                          • 10. Re: ExecuteSQL advanced query limits?
                            mikebeargie

                            This would only work for me if I could return data from executeSQL in an array. My goal was to use the calculation in a chart, hence the need to have a list returned by the function (of the latest value, by group)

                             

                            It might just not be possible, in which case the backup plan is to script it. I had rather not wanted to do this due to not wanting it to be tax heavy on the system, (compiling data from ~100 races and it needs to update per-minute).

                             

                            Hopefully I can optimize the scripting if it's not possible via ExecuteSQL.

                            • 11. Re: ExecuteSQL advanced query limits?
                              gwinzeler

                              what is the top row of each group?   the max the min the alpha - need more info

                              • 12. Re: ExecuteSQL advanced query limits?
                                mikebeargie

                                Sorry, it was in my original post up top.

                                 

                                I am grabbing a numeric count to use in a chart from the top row. The top row becomes the top row by sorting DESC from a serial number. The data also needs to be grouped by a countyID and candidateID.

                                 

                                The return should be the sum of the most recent count values from each county related to a queried raceID.

                                • 13. Re: ExecuteSQL advanced query limits?
                                  Ronnie

                                  Try this:

                                   

                                  SELECT SUM(candidateCount)

                                  FROM RACE_DATA

                                  WHERE a_kf_raceIdKey = 1 AND

                                  a_kf_countyIdKey = (

                                            SELECT MAX(a_kf_countyIdKey)

                                            FROM RACE_DATA

                                            WHERE a_kf_raceIdKey = 1 )

                                  • 14. Re: ExecuteSQL advanced query limits?
                                    mikebeargie

                                    Hi Ronnie,

                                     

                                    This only returns a sum of the highest county's values (Since there are 88 counties, this will always return a total from county 88's entries). I need a return delimited list of the most recent entry from each county.

                                     

                                    From the feedback above showing that there is no way to filter a first row out of a set of returned/grouped records, I have already tried a different approach of scripting to build a global variable, using ExecuteSQL inside the loop to speed it up.

                                     

                                    Thanks!

                                    1 2 Previous Next