1 2 Previous Next 26 Replies Latest reply on Aug 8, 2012 6:53 PM by Oliver_Reid

    EXCEPT in ExecuteSQL  (fmp12) supported?

    Oliver_Reid

      I want to do

       

      Select ......

       

      EXCEPT

       

      Select ...

       

       

       

       

      Where each Select returns a single column, (both with the same name) to find the values returned by the first query that are not returned by the second

       

      But I get a "?"

       

       

      If I use UNION instead I get the union of the two results -- is EXCEPT not supported?

       

       

      The situation is like as library where I want to find the ids of all copies of a certain book title that are not checked out.

       

      If it's checked out there is a record in a book-borrowing table with the book id, and returned date is empty.

       

      So I want to find the ids all copies of a specified title EXCEPT those that are refrenced in borrowing record where the returned date is empty.

       

      (Note most copies have many borrowing records with a non-empty returned date)

       

      I have done this with various list calcs and TO's but I want to do it with ExcuteSQL and simplify my TOG

        • 1. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
          BeatriceBeaubien

          Hi Oliver,

           

          This link may be useful in deciphering the "?" in an ExecuteSQL result:

          <http://www.fmfunctions.com/fid/335>

           

          "This function will help you to debug your SQL statement if it returns a "?"..."

           

           

           

          Best wishes,

           

          Beatrice Beaubien, PhD

          i2eye, Toronto, Canada

           

          FileMaker Business Alliance

          FileMaker 12 Certified Developer

          Knowledge Translation Certified Professional

           

          On Aug 6, 2012, at 19:47, Oliver_Reid wrote

           

           

          created by Oliver_Reid in Advanced Discussion - View the full discussion

          I want to do

           

          Select ......

           

          EXCEPT

           

          Select ...

           

          Where each Select returns a single column, (both with the same name) to find the values returned by the first query that are not returned by the second

           

           

          But  I get a   "?"

           

           

          /snip

          • 2. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
            Oliver_Reid

            Thanks Beatrice, but I just get the message

             

             

            "There is an error in the syntax of the query."

             

             

            Which I do not believe to be true

            • 3. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
              pthomas

              Hi Oliver,

               

              It wouldn't surprise me if EXCEPT is not supported.

               

              Have you tried using an EXISTS clause in your WHERE statement which should give you the result you are looking for?

               

              Something along the lines of:

               

              SELECT

                B.Id

              FROM

                Books B

              WHERE

                NOT EXISTS (

                  SELECT

                    'X'

                  FROM

                    BookBorrowing BB

                  WHERE
                    BB.Id = B.Id

                    AND BB.ReturnedDate = '')

               

              Obviously you will need to substitute the names of your fields and tables!

               

              Cheers,

               

              Paul.

              • 4. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                Oliver_Reid

                Thanks Paul

                 

                But it looks like NOT EXISTS is not supported either!

                 

                Is there a reference artcle anywhere on exaclty what SQL syntax is supported by executesql ?

                • 5. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                  pthomas

                  I actually tried the NOT EXISTS statementent before posting and it worked ok for me (in the data viewer anyway).

                   

                  Maybe check that you have the syntax correct?

                  • 6. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                    Oliver_Reid

                    The actual query is

                     

                    Select

                     

                     

                    a.a_recid from Inventory a

                    where a.aa_type_id=?

                    and a.aa_fl_available ='Yes'

                    and NOT EXISTS

                    (SELECT * FROM  Deliveries b  where b.aa_c_item_id_if_not_picked_up=a.a_recid)

                     

                     

                    Inventory == books    deliveries == borrowing

                     

                    The query through to 'Yes' works fine.  A value is supplied for the ?

                    • 7. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                      pthomas

                      Nothing obvious looks wrong with that query.

                       

                      What are the data types of the a_recid and aa_c_item_id_if_not_picked_up fields, are they both set to number?

                       

                      Also, are you able to post a screenshot of the query as written in FileMaker?

                      • 8. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                        Oliver_Reid

                        The fields are bother numeric, stored and indexed

                        See below. Note: now this throws FM into a loop and hangs the application when the field is displayed!

                        The purpose is to find a list of items of a given type (Item) from a rental inventory  that are available for delivery -- i.e. they are available (aa_fl_avaiable='Yes') and also not currently with a customer :  i.e. have not been delivered to a customer and not picked up again. Just the kind of thing SQL shoudl be good for ?

                         

                         

                         

                        Untitled 2.png

                        • 9. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                          greglane

                          Hi Oliver,

                           

                          If I understand your description correctly, either of these queries (change the field names as needed) should give you the results you're after.

                           

                          SELECT id

                          FROM inventory

                          INNER JOIN deliveries

                            ON inventory.id = deliveries.inventory_id

                          WHERE return_date IS NULL AND available='yes'

                           

                          SELECT id

                          FROM inventory i

                          WHERE available='yes' AND

                          EXISTS (

                            SELECT * FROM deliveries d

                            WHERE d.return_date IS NULL AND

                              i.id = d.inventory_id

                          )

                           

                          If it's possible to have multiple matching delivery records for a particular inventory record, change the first line of the first query to "SELECT DISTINCT id".

                          • 10. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                            beverly

                            I'm not at all clear what you are trying to return. The other posts did not help either.

                             

                            GIVEN this statement:

                            Where each Select returns a single column, (both with the same name) to find the values returned by the first query that are not returned by the second

                             

                            I wonder if "NOT IN" is what you are seeking? Get the second query first, use it's values in the first query with NOT IN.

                             

                            SELECT id FROM a

                            WHERE id NOT IN (SELECT id FROM b WHERE .... )

                            ... other find criteria here if needed on 'a' ...

                             

                            Beverly

                             

                            EDITED VERSION:

                             

                            SELECT a.id FROM a

                            WHERE a.id NOT IN (SELECT b.id FROM b WHERE ... )

                            ... other ...

                             

                            the "id" would be too confusing if not revised.

                             

                            Message was edited by: Beverly Voth

                            • 11. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                              pthomas

                              Hi Beverly,

                               

                              While the IN statement should work, I believe that the EXISTS statement is much faster to process.

                               

                              That is the case for say a SQL Server anyway, FileMaker may evaluate the commands differently - I haven't done any testing to confirm which method works faster in FileMaker.

                               

                               

                              Oliver - how many records do you have in each of your tables?

                               

                              Could it be that the statement is working, but due to the size of your tables it is taking a very long time to process which is making it look like FileMaker has hung?

                               

                              One way to test would be to run a similar query on a couple of very small tables just to confirm that you can get the syntax correct!

                               

                               

                              Reading over my notes from DevCon I see that the ExecuteSQL command will download the entire record when you reference one field from the table, with that in mind it may actually be faster to execute a query more like:

                               

                              SELECT 

                                a.a_recid

                              FROM

                                Inventory a

                              WHERE

                                a.aa_type_id=?

                                AND a.aa_fl_available ='Yes'

                                AND (

                                  SELECT

                                    COUNT(*)

                                  FROM
                                    Deliveries b

                                  WHERE

                                    b.aa_c_item_id_if_not_picked_up = a.a_recid ) = 0

                               

                              The COUNT(*) command does not force the download of the entire record data, so it should be much faster to run.

                               

                              Once again I haven't had a chance to test this out sorry as my FM12 machine is still being worked on by our system adminstrator.

                               

                              Cheers,

                               

                              Paul.

                              • 12. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                                beverly

                                Sure, while EXISTS seems to be one of the Reserved SQL Keywords in FMP, it doesn't seem to be supported in FMP...

                                 

                                Or do you get different results, Paul?

                                 

                                Beverly

                                • 13. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                                  pthomas

                                  I tried out both EXISTS and NOT EXISTS clauses yesterday in FileMaker and they worked for me.

                                   

                                  Cheers,

                                   

                                  Paul.

                                  • 14. Re: EXCEPT in ExecuteSQL  (fmp12) supported?
                                    beverly

                                    Paul, you replied to Oliver yesterday, that you tested these in the Data Viewer. did you try them in a script or calculation?

                                     

                                    Beverly

                                    1 2 Previous Next