1 2 Previous Next 19 Replies Latest reply on Mar 3, 2016 3:42 AM by Ben_B94

    SQL Script

    Ben_B94

      Tried to edit an SQL script that was provided but one of the variables $currentAssignedItems, keeps returning ? This was after my DB changed from one table broken down into several so probably the wrong field name somewhere but due to a lack of understanding I'm having trouble. I don't really understand the set field part in the loop and secondly does scripting with executeSQL allow you to just specify fields without having the table prefix beforehand even for fields in different tables?

        • 1. Re: SQL Script
          Mike_Mitchell

          Ben_B94 wrote:

           

          [D]oes scripting with executeSQL allow you to just specify fields without having the table prefix beforehand even for fields in different tables?

           

          No SQL query allows you to bypass specifying table names, with the exception that you can omit them if all the fields in the query come from the same table. I usually use table aliases to make the queries shorter and easier to read, like this:

           

          SELECT so.ToolID, so.DIX_Num, so.Model, so.Category, so.Serial_Number FROM SignOut so WHERE so.Name = ?

           

          Of course, if you are coming from different tables, then you have to create a join between them in the query. You can do this either explicitly or implicitly. The latter is simpler, but only works for simple joins (like where there are only two tables). That might look something like this:

           

          SELECT so.ToolID, so.DIX_Num, m.Model, m.Category, so.Serial_Number FROM SignOut so, Models m WHERE so.ModelID = m.ModelID AND m.Name = ?

           

          If you need additional tables included, it usually gets a bit more complex. We would need to see your table structure to know how to help.

           

          Mike

           

          P.S. It's usually better to post your screen shots as images (little camera icon) rather than sticking them in a Word document. That way, people can see the image without having to download and open the document.

          • 2. Re: SQL Script
            beverly

            1. It's difficult to say, but sometimes a word in a table or column name is "reserved" or otherwise needs to be "escaped".

             

            "SELECT ...

            WHERE \"Name\" = ?

            ..."

             

            And there may be other places that need to be escaped.

             

            2. If you've changed something, you might verify that you have the correct T.O. (table occurrence) on the RG (relationship graph) in the file/db from which you are calling the ExecuteSQL().

             

            3. I don't understand what the Set Field is doing either. It would surely over-write each loop.

             

            beverly

            • 3. Re: SQL Script
              Ben_B94

              In the list of reserved words there is names but not name so does my field name, Name, need to be escaped? 

              • 4. Re: SQL Script
                Ben_B94

                Thanks, It looks like I need a join then in that currentAssignedItems variable as the WHERE name part references different table!

                • 5. Re: SQL Script
                  beverly

                  If in doubt, escape.

                  beverly

                  • 6. Re: SQL Script
                    coherentkris

                    FM sql reserved words

                    http://help.filemaker.com/app/answers/detail/a_id/12097/~/reserved-words-in-filemaker-pro

                     

                    Name" should be safe but...

                     

                    List of SQL reserved words | Drupal.org

                     

                    Does the SignOut table have fields ToolID, DIX_Num, Model, Category, Serial_Number and Name?

                     

                    Can Set Variable ( $currentName ; GetValue ( $nameID ; $counter ) ever return ""?

                     

                    Because $currentName is used as an argument for ExecuteSQL() $currentName = "" may cause error although i have never tested null/empty string args in ExecuteSQL().

                    • 7. Re: SQL Script
                      Ben_B94

                      Changed it to tools as its the tools table that contains those fields, I was confusing myself because i put them in the SignOut layout and thought they were in there.

                      • 8. Re: SQL Script
                        Ben_B94

                        Sorry name field is in staff. The name returns ok, literally the only part that doesnt appear is the items in the email.

                        • 9. Re: SQL Script
                          Ben_B94

                          For your table aliases do you specify an earlier script line saying select a,b,c from tools as to etc? or on the same line?

                          • 10. Re: SQL Script
                            Ben_B94

                            Oh I see it now from your reply

                            • 11. Re: SQL Script
                              coherentkris

                              Select * FROM Table1 will only return values from Table1.. to retrieve values from anything but Table1 you have to use SQL Joins or other SQL methods.

                              • 12. Re: SQL Script
                                beverly

                                As Mike said, the joins can be implied.

                                 

                                " SELECT field1, field2, field3, 'constant'

                                FROM table1, table2

                                WHERE table1.field1 = table2.field2

                                     AND table1.field3 = ? "

                                 

                                may be perfectly ok. I depends on what you need returned. An explicit JOIN may be LEFT OUTER JOIN, for example, to get all parents whether they have children or not.

                                 

                                All tables must be on the RG, but need not be related to be used in ExecuteSQL().

                                ALIAS (table AS ...) need not be used IF the columns/fields are uniquely named. But placing the table name (or alias) with the dot+column name can provide clarity in the SQL statement.

                                 

                                beverly

                                • 13. Re: SQL Script
                                  Ben_B94

                                  How do you write a join from 2 tables away. I have staff >---- Location -----< Tools. Most of the fields are from tools but the name field is pulled from staff. 

                                  • 14. Re: SQL Script
                                    beverly

                                    what fields do you need? from which tables? and what fields are related?

                                     

                                    I agree, your screen shot was difficult to view. Can you answer the above in text and the query can be written.

                                    beverly

                                    1 2 Previous Next