5 Replies Latest reply on Jan 11, 2013 1:44 PM by philmodjunk

    SQL engine buggy - Correlated subquery in HAVING statement

    electric_soul

      Summary

      SQL engine buggy - Correlated subquery in HAVING statement

      Product

      FileMaker Pro

      Version

      12.0v3 & 11.0v3

      Operating system version

      Windows xp SP3

      Description of the issue

      SQL Statement gives wrong results.

      With "Filemaker Pro 11 Adv" I used the baselements plugin to access the sql engine. With "FM Pro 12 Adv" I used the executeSQL() function. Both versions gave the same results.

      Steps to reproduce the problem

      Download this file
      http://www.peejeshare.com/files/363438503/testsql.fp7.html

      open dataviewer and run the following sql statement.

      SELECT oli.ID_Product, sum( oli.qty )
      FROM material oli
      WHERE oli.ID_Order = 'SQ-000568-01'
      GROUP BY oli.ID_Product
      HAVING sum( oli.qty ) > (SELECT sum( ap2.Quantity) FROM Artikelposten ap2 WHERE ap2.id = oli.ID_Product)
      ")

      Expected result

      1
      B-FC-5114     10
      B-FC-5115     10
      B-FC-5120     10
      B-FC-5200     10
      B-FC-5201     1
      B-FC-5250     1
      B-FC-5300     3

      Actual result

      1
      APS-KIT0     25
      B-FC-5114     10
      B-FC-5115     10
      B-FC-5120     10
      B-FC-5200     10
      B-FC-5201     1
      B-FC-5250     1
      B-FC-5300     3

      The ID APS-KIT0 should not appear.


      I've tested it with MySQL on XAMPP. MySQL gives the following result.

      B-FC-5114     10
      B-FC-5115     10
      B-FC-5120     10
      B-FC-5200     10
      B-FC-5201     1
      B-FC-5250     1
      B-FC-5300     3

        • 1. Re: SQL engine buggy - Correlated subquery in HAVING statement
          philmodjunk

               I'd be interested in hearing from the FileMaker Techs whether FileMaker's ExecuteSQL function suports nesting one Select statement inside the other like this.

               If you remove that part of the expression and subsitute a constant for testing purposes, do you get a result that is correct for that constant value?

               If so, using ExecuteSQL, you may need to use this expression:

               ExecuteSQL ( "

               SELECT oli.ID_Product, sum( oli.qty )
               FROM material oli
               WHERE oli.ID_Order = 'SQ-000568-01'
               GROUP BY oli.ID_Product
               HAVING sum( oli.qty ) > ?

               "; ; ; ExecuteSQL ( "SELECT sum( ap2.Quantity) FROM Artikelposten ap2 WHERE ap2.id = oli.ID_Product" )

               )

               It's worth a try any way...

                

          • 2. Re: SQL engine buggy - Correlated subquery in HAVING statement
            electric_soul

                 Hey

                 Thanks for looking at this :)

                  

            Replacing with constant

                 If I replace oli.ID_Product with 'APS-KIT0' and play around with the quanttiy, I always get correct results. So it seems, that the correlation part is broken/not avaiable.

                  

            Using your suggestion for execute():

                 This doesn't work because we get two seperate SQL queries. The second shorter query does not know what "oli.ID_Product" is. Meaning, there is no correlation.

            Subqueries

                 When it comes to nested queries, how should I know which ones are supported? I consulted the odcb/jdcb guide and functions reference. They do not say that you can have a nested query in the WHERE statement, but it works :)  Do you know any other sources ??

                  

            Result?

                 Anyway.....  when it come to the new sql function, it would be a blast if it could perform such basic tasks. Live would be come extremly easy :)

                 Sooo, have you informed the technicians? Should I send a request?

                  

                 take care

                 bye

            • 3. Re: SQL engine buggy - Correlated subquery in HAVING statement
              philmodjunk
                   

                        I consulted the odcb/jdcb guide and functions reference. They do not say that you can have a nested query in the WHERE statement, but it works :)  Do you know any other sources ??

                   I do not. I have previously indicated to the folks at FileMaker Inc. that they need more complete documentation for this new function and what can or cannot be done with it.

                   They also need to add a wizard for building and testing SQL queries.

              • 4. Re: SQL engine buggy - Correlated subquery in HAVING statement
                electric_soul

                     A wizard for building sql queries? I'd say there are pleny of docs on the net to learn from....well. But a sql reference for FM would be nice indeed :)

                      

                     I requested correlated sql queries as a featrure. I hope they realize that. That'd be fantastic :)

                      

                     bye bye

                • 5. Re: SQL engine buggy - Correlated subquery in HAVING statement
                  philmodjunk
                       

                            there are pleny of docs on the net to learn from

                       Of course they are, but every SQL engine that I've ever worked with comes with it's own "flavor" of SQL. Stuff that works in one doesn't work in the other without modification.

                       And for me, there's no substitute for being able to build a trial, expression with actual tables and then be able to inspect a table view of the results while being able to iterate back and forth until it's all just right. Very much like testing out a complex expression in FileMaker Advanced's Data Viewer.

                       The Data Viewer is actualy useful for testing ExecuteSQL, but you get that frustrating ? so easy and a similar widgit but with tools for setting up the joins with correct syntax would save a lot of time.

                       Most SQL DB's have such a tool for precisely that reason. SeedCodes SQL Explorer is a good start, but I want more capabilities than it has coupled with better documentation.