7 Replies Latest reply on Oct 21, 2013 2:50 PM by philmodjunk

    ExecuteSQL Help

    bvondeylen

      Title

      ExecuteSQL Help

      Post

           I am attempting to display a Teacher Name with a student from our Infinite Campus (Microsoft SQL) database within FileMaker Pro 12.

           The 3 Tables in MS SQL are:

           Student          Roster          Section

           personID       personID     

                                   sectionID     sectionID

            

           firstName, lastName come from Student

           teacherDisplay comes from Section

           A sample student has an ID (studentNumber) of 37972 (studentNumber is in student table)

           This student is part of Section 859456

           When I manually do the Search in the 3 tables, I get what I want, but when I tried the following ExecuteSQL I have no luck

            

           ExecuteSQL ( 
           "
           SELECT s.lastName, s.firstName, sec.teacherDisplay 
           FROM student AS s
           JOIN roster AS r ON s.personID = r.personID WHERE s.studentNumber = '37972' AND
           JOIN section AS sec ON sec.sectionID = r.sectionID WHERE sec.sectionID = '859456' 
           " ;
            "" ; "" )

        • 1. Re: ExecuteSQL Help
          philmodjunk

               "no luck" as in no results returned, the wrong results returned or the infamous question mark that usually means that you have a syntax error in your SQL?

               I've never seen a WHERE clause used between join clauses like you have here so that part of the expression looks suspicious to me, but maybe that's just my inexperience with SQL.

               If you are getting the question mark, maybe this form would work:

               SELECT s.lastName, s.firstName, sec.teacherDisplay 
               FROM student AS s
               JOIN roster AS r ON s.personID = r.personID
               JOIN section AS sec ON sec.sectionID = r.sectionID
               WHERE s.studentNumber = '37972' AND sec.sectionID = '859456'
          • 2. Re: ExecuteSQL Help
            bvondeylen

                 I tried several varieties of the SQL command. All give me the ?

                 Any other ideas?

            • 3. Re: ExecuteSQL Help
              philmodjunk

                   That result becomes very frustrating as it provides zero clue as to where the error lies. angry

                   You might try using SeedCodes free SQL Explorer file to build your query and see if it works for you. If it does, you can then compare the SQL it produces with yours to try and spot what went wrong in the original.

              • 4. Re: ExecuteSQL Help
                bvondeylen

                     I guess I didn't reply to your post. I did try your SQL statement as well which resulted in the ? as well.

                     This SQL Command works

                      

                     ExecuteSQL ( 
                     "
                     SELECT s.lastName, s.firstName
                     FROM student AS s
                     WHERE s.studentNumber = '37972'
                     " ;
                      "" ; "" )
                      
                     BUT this does not work (result ?)
                      
                          ExecuteSQL ( 
                          "
                          SELECT teacherDisplay
                          FROM Section
                          WHERE sectionID = '859456'
                          " ;
                           "" ; "" )
                           
                          There is a table occurance called Section
                          There is a field within the table occurance Section called teacherDisplay
                          There is a field within the table occurance Section called sectionID 
                          AND there is a record in this table with a sectionID of 859456
                           

                      

                • 5. Re: ExecuteSQL Help
                  philmodjunk

                       You did reply and as I can't spot any obvious errors, I've suggested a different approach that you can try out if interested.

                  • 6. Re: ExecuteSQL Help
                    bvondeylen

                         SQL Explorer is a cool program. Just trying it out right now. For whatever reason, SQL is extremely slow. I am trying to run a query from SQL Explorer right now, and it has been over 10 minutes and I am still waiting for the result. I am hoping it works.

                         Noticed SQL Explorer likes to put parenthesis around all field names and table names (maybe that is the reason mine didn't work???).

                         I will post when I do find a solution though. Thank you

                    • 7. Re: ExecuteSQL Help
                      philmodjunk

                           I think you mean that SQL Explorer double quotes all field and table names using \" fieldname \". Since it's very easy to have names that are either a reserved SQL word or that trip a syntax error such as the one tripped with a field named __pkContactID, it quotes everything to be on the safe side.