8 Replies Latest reply on Dec 4, 2014 7:08 AM by MorkAfur

    SQL GROUP BY ISSUE

    MorkAfur

      Title

      SQL GROUP BY ISSUE

      Post

      I'm confused why FMP doesn't like the following SQL:

      SELECT YEAR(date_first_received), count(year(date_first_received)) as cnt from vendors 
      group by year(date_first_received)order by date_first_received DESC

      In particular, it doesn't seem to like my GROUP BY expression.

      The date_first_received field is a standard date field with no strict data formatting (per FMP's SQL reference).

      This same type of query works fine in, say, MySQL or Oracle, so I'm wondering if there is some GROUP BY restriction in FMP. I can't seem to find many FMP-specific examples.

      -----

      I'm testing these queries in a separate database console so I can use JDBC and also since I don't just get FMP's maddening "?" result with zero helpful information. :(

      (Just to pile on for a second, since I've already requested an increase in SQL functionality, IMHO, FMP's current "Data Viewer" window is so tiny, windows and fonts can't be resized, non-helpful "?" error messages and the like, it's really not useful for SQL work against the database (for other than the simplest queries)-- a separate database console is the only way to go that I've found.)

      ----

      Thanks in advance.

      - m

        • 1. Re: SQL GROUP BY ISSUE
          philmodjunk

          Here's a custom function that was shared here in the forum that I think all developers working with ExecuteSQL should have and use:

          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

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

          You can set up the Data Viewer to test your SQL query like this:

          SQL.Debug (
          ExecuteSQL ( "
          SELECT.....
          )
          )

          then you click "Monitor", then click the pencil icon to re-open the watch expression for editing and check the Evaluate box at the bottom. This little gem then magically displays an honest to goodness error message instead of the dreaded ? that just might clue us in as to why there's a problem with the Query syntax.

          My second suggestion would be to try defining a calculation field using the Year function to extract the year and then try grouping by that field instead of a calculation to see if the GROUP BY clause can't properly evaluate your expression.

          • 2. Re: SQL GROUP BY ISSUE
            MorkAfur

            Hey Phil,

            Thanks for the custom function idea yet I prefer to work with a remote console (for me, that means using a Java IDE) since it gives me all the error information without all this FMP setup. Plus, you can browse tables and see data directly, which you can't do in FMP unless you go to a layout. But then, if you're in the mostly modal debug window, then, ....

            Doing this query in a Java IDE with a JDBC connection to the FMP datbase with this query:

            SELECT YEAR(date_first_received), count(year(date_first_received)) as cnt from vendors 
            group by 
            year(date_first_received) order by date_first_received DESC
            
            

            Gives this error (referring to the group by expression on the third line):

            [2014-12-03 10:55:28] [08007] [FileMaker][FileMaker JDBC] FQL0001/(3:2): There is an error in the syntax of the query.

            -------------

            My question was really about possible FMP's GROUP BY limitations if any since this simple query doesn't work.

            Being able to get a count of items by year is such a simple requirement, I'm still baffled why FMP isn't able to do this.

            I could post a sample solution if necessary to demonstrate.

            Thanks,

            -- m

            • 3. Re: SQL GROUP BY ISSUE
              philmodjunk

              Yet your error information is not in the context of using ExecuteSQL and that might be a crucial difference here.

              I repeat my suggestion that you replace the Year calculation with a reference to a calculation field that uses the year function to see if perhaps the Group By clause in FileMaker does not support using a calculated expression.

              • 4. Re: SQL GROUP BY ISSUE
                MorkAfur

                Phil,

                I sincerely appreciate your terrific replies, as always.

                Yes, your suggestion does work, as if there were any doubt, though I was trying to avoid creating a calc field and instead stick to SQL.

                Using your idea, however, works fine:

                select YEAR_OF_FIRST_CONTACT_DATE, COUNT(YEAR_OF_FIRST_CONTACT_DATE) as numberContacts from VENDORS GROUP BY YEAR_OF_FIRST_CONTACT_DATE
                ORDER BY YEAR_OF_FIRST_CONTACT_DATE ASC

                Returns:

                2010,     9
                2011,     23
                2012,     1
                2013,     3
                2014,     3

                My goal was to be able to just rocket through queries without having to drop back into FMP to fill in (apparent) gaps in its SQL implementation, but whatever. It's nice that FMP has these easy workarounds, when necessary, too. :)

                Thanks again!

                - m

                • 5. Re: SQL GROUP BY ISSUE
                  philmodjunk

                  I didn't know if it would work or not. I was hoping it would work based on a half remembered post made by someone else.

                  I just researched Group By in FileMaker's SQL reference document and found:

                  The GROUP BY clause specifies the names of one or more fields by which the returned values
                  should be grouped. This clause is used to return a set of aggregate values. It has the following
                  format:
                  GROUP BY columns
                  columns must match the column expression used in the SELECT clause. A column expression
                  can be one or more field names of the database table separated by commas.

                  By that info, I'd predict that the following would work:

                  SELECT 
                  YEAR(date_first_received as yr, ( count(year(date_first_received)) as cnt from vendors 
                  group by yr 
                  order by date_first_received DESC
                  • 6. Re: SQL GROUP BY ISSUE
                    MorkAfur

                    Hey,

                    Yeah, I checked the same document.

                    What you wrote above is standard GROUP BY stuff and, unfortunately, your query, after I corrected the syntax, does not work:

                    Updated query:

                    SELECT YEAR(date_first_received) as yr, count(year(date_first_received)) as cnt from vendors
                    group by yr
                    order by date_first_received DESC
                    
                    

                    Error Result:

                    [FileMaker][FileMaker JDBC] FQL0007/(2:10): The column named "yr" does not exist in any table in the column reference's scope.

                    Hopefully, somebody from FM will jump in here now...

                    Hello, FM???

                    Thanks,

                    -m

                    P.S. Check out the Free Eclipse IDE and its database plug-in: http://www.sqlexplorer.org/screenshots.php

                    • 7. Re: SQL GROUP BY ISSUE
                      philmodjunk

                      People from FM do not regularly monitor this thread. I suggest posting this as a possible bug over in Report an Issue. You can post a link to here to save retyping everything.

                      • 8. Re: SQL GROUP BY ISSUE
                        MorkAfur

                        Done. Thanks Phil.

                        Let me know if you check out the Eclipse data console. I haven't used that particular one, but I think you'll find for a lot of ad-hoc queries, having a facility like that is hard to beat. For application work, the facility inside FMP is "OK", but the Data Viewer is so weak overall -- my least favorite part of the entire product -- due in part to the tiny small, non-resizable, output window at the bottom that makes serious work much more difficult.

                        Since FM distributes the JDBC "Jar" file, all you need to do is add it to Eclipse. and then create a connection to the open FMP database (after allowing JDBC/ODBC, of course, in the FMP app). It's super easy. Let me know if you have any questions. Unlike ODBC, no up front configuration is necessary on the machine to connect to the DB (using JDBC).

                        Check it out. :)

                        Later....

                        - m