13 Replies Latest reply on Mar 12, 2015 8:02 AM by beverly

    eSQL - syntax error (I think)

    davehob

      In a relationship of People (PPL) > Ethnicities (ETH) > Ethnicity_groups (EGR), I want to do an eSQL query to find the number of People in each Ethnicity_group.

       

      This is where I've got to so far. (PPL::FILTERIDS holds a list of keys from a found set).  And the step before this, which is a simpler query of just ethnicities (i.e. a "two level" query) works fine - it's the addition of the third level, EGR, which has caused the problem.

       

      I'm hoping that it's a simple syntax error, as it currently returns "?", but I can't see it.  Any pointers much appreciated.

       

      Let (

      [

      ~idList = Substitute ( PPL::FILTERIDS; "¶"; "','");

      ~query =

      "SELECT COUNT (*)

      FROM PPL p, ETH e, EGR g

      WHERE p.id IN ('" & ~idList & "')

      AND p.id_ETH = e.id

      AND e.id_EGR = g.id

      GROUP BY g.group" ;

      ~result = ExecuteSQL( ~query ; "" ; ""; "" )

      ];

      ~result)

        • 1. Re: eSQL - syntax error (I think)
          Markus Schneider

          SPecial characters in fieldnames (blanks..) needs to be escaped (in apostrophes) ie \"PPL p\"

          (edit: backslash...)

          • 2. Re: eSQL - syntax error (I think)
            davehob

            Thanks Markus, but that's not it - the 'PPL p' in this case is just defining an alias for PPL (shortened form of 'PPL AS p').

            • 3. Re: eSQL - syntax error (I think)
              Markus Schneider

              ouch - did not recognize that in the hurry.. sorry

              • 4. Re: eSQL - syntax error (I think)
                beverly

                Sounds like time for a JOIN!

                 

                Let (

                    [ ~idList = Substitute ( PPL::FILTERIDS; "¶"; "','")

                    ; ~query =

                        " SELECT g.group, COUNT( p.id )

                        FROM PPL as p

                            JOIN ETH as e ON p.id = e.id

                            JOIN EGR as g ON e.id = g.id

                        WHERE p.id IN ('" & ~idList & "')

                        GROUP BY g.group "

                    ; ~result = ExecuteSQL( ~query ; "" ; "" )

                    ]; ~result

                )

                 

                note that I added the group field to the result and you had one too many "" in the eSQL parameters. I also did not use the "*" in the COUNT, just a field that was going to be counted and "id" seemed reasonable.

                 

                The JOINS are going to be very like a relationship graph connections, if you use that method on a found count and had a summary field.

                 

                beverly

                • 5. Re: eSQL - syntax error (I think)
                  davehob

                  Hmmm, thanks Beverley - I'm still getting an error.  Should I not be using the foreign keys in the joins (i.e. p.id_ETH = e.id, and e.id_EGR = g.id)? That said, I have tried it that way, and still I'm getting '?' when I evaluate it, so I don't know where I'm going wrong.  I've checked field names etc., and can't see anything obvious.

                   

                  Dave.

                  • 6. Re: eSQL - syntax error (I think)
                    beverly

                    Yes, sorry, Dave, I copied your AND clauses incorrectly. This should be:

                     

                    FROM PPL as p

                        JOIN ETH as e ON p.id_ETH = e.id

                        JOIN EGR as g ON e.id_EGR = g.id

                     

                    if those are the correct fields and the correct relationships (as they would be in a relationship graph)

                    beverly

                    • 7. Re: eSQL - syntax error (I think)
                      nicolai

                      Check that  id_EGR in ETH and ID in ERG are the same data type.

                      • 8. Re: eSQL - syntax error (I think)
                        user19752

                        group is a reserved word so need to be quoted if you use as table/column name.

                        • 9. Re: eSQL - syntax error (I think)
                          davehob

                          Thanks, user19752, that was the problem.  Changed the field name to ethnicityGroup, and all's well.  (I must now check the rest of the application for reserved words!)

                          Thanks again,

                          Dave.

                          • 10. Re: eSQL - syntax error (I think)
                            beverly

                            I wouldn't use 'id' (any case) as a field either. I also make it the key field, but with the table:

                                 customers -> customerID

                                 invoices -> invoiceID

                                 etc.

                             

                            When used in related tables (as foreign key), it's pretty easy to see that the "id" is the parent's primary key:

                                 addresses -> addressID (primary key), customerID (foreign key)

                                 etc.

                             

                             

                            beverly

                            On Mar 12, 2015, at 5:28 AM, Dave Hobson <noreply@filemaker.com> wrote

                             

                             

                            eSQL - syntax error (I think)

                            reply from Dave Hobson in Discussions - View the full discussion

                            Thanks, user19752, that was the problem.  Changed the field name to ethnicityGroup, and all's well.  (I must now check the rest of the application for reserved words!)

                            Thanks again,

                            Dave.

                             

                            • 11. Re: eSQL - syntax error (I think)
                              jameshea

                              Hey Bev,

                               

                              Thanks for your comments. To group the IDs together, how about using IDAddress, IDCustomer, etc.?

                               

                              That should work nicely in the FM environment and also provide the easy identification you'd like in FM and SQL calcs. I'd suggest that the IDs use the proper table name (no spaces) so that if a calculation is needed to set field by name, a variable can capture the proper table name and then have it concatenated to the literal string "ID". (i.e. "ID" & $TableName)

                               

                              Hope this helps.

                               

                              James

                              • 12. Re: eSQL - syntax error (I think)
                                davehob

                                Thanks for the tip, Beverly - is that because you feel it keeps things clearer?  Or is there more serious trouble lurking (like the reserved words thing)?

                                Dave.

                                • 13. Re: eSQL - syntax error (I think)
                                  beverly

                                  Good suggestion JH! Yes I believe I tried that once. Sorting by "ID...." is rarely my problem. $TableName & "ID" also works when needed.

                                   

                                  davehob mostly because:

                                       table1::id

                                       table2::id

                                       table3::id

                                  is less descriptive than

                                       customers::customerID

                                       addresses::addressID

                                       orders:orderID