10 Replies Latest reply on Mar 2, 2015 2:28 PM by BruceHerbach

    ExecuteSql Command

    Jason_Farnsworth

      I have a Executesql calculation I am trying to make work and I am going in circles with it

       

      I am currently using

       

      ExecuteSQL ( " SELECT CTR_Warning_Type

      FROM JOB_SES

      WHERE Letter_AGE = 1 " ; "..." ; ""  )

       

      Which gives me a list of all "Warning Types" across all jobs that meet the Letter_Age = 1 requirement. I am performing this calculation in a job table, Field.

       

      What I would like to realize is...

       

      Only the "Warning Types" for that job (not all jobs) that meet the Letter_Age = 1 requirement.

       

      What am I doing wrong? Should I not be using this via a Field Calculation?

       

      Thanks in advance,

       

      Jason Farnsworth

        • 1. Re: ExecuteSql Command
          Mike_Mitchell

          Jason -

           

          ExecuteSQL ( ) ignores the current context. It just looks at the tables (TOs) on the Graph. So if you tell it you want a result, you have to tell it to constrain the result to the current record. Try modifying your calculation to this:

           

          ExecuteSQL (

          "SELECT CTR_Warning_Type FROM JOB_SES WHERE Letter_AGE = 1 AND JOB_SES_ID = ?" ;

          "..." ;

          "" ;

          JOB_SES_ID

          )

           

          The "?" is a parameter that tells the calculation to substitute the value of the current JOB_SES_ID into the query. You should use whatever the correct field is for the unique identifier in your table.

           

          HTH

           

          Mike

          • 2. Re: ExecuteSql Command
            BruceHerbach

            Jason,

             

            Mike gives you a good example.  You might look at Seedcode's SQLExplorer.  It is a free download.  You can use it to craft the query you need then copy and paste it into your solution.  I find it to be a great tool.

             

            Another great help is Beverly Voth's Execute SQL missing manual.

            The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

             

            HTH

            • 3. Re: ExecuteSql Command
              Jason_Farnsworth

              ExecuteSQL (

              "SELECT CTR_Warning_Type FROM JOB_SES WHERE Letter_AGE = 1 AND JOB_SES_ID = ?" ;

              "..." ;

              "" ;

              JOB_SES_ID

              )

               

              Mike, Thank you the input.

               

              I am not quite there yet for some reason, This is what I currently have...

               

              ExecuteSQL ( " SELECT JOB_SES::CTR_Warning_Type

              FROM JOB_SES

              WHERE Letter_Age = 1 AND JOB_SES::_wf_JOBID = ? " ; "..." ; "" ; JOB::zwp_JOBID )

               

              With the above I get a result of "?" I should get a list of (4) items

               

              Thanks,

               

              Jason Farnsworth

              • 4. Re: ExecuteSql Command
                Mike_Mitchell

                The "?" in the result is telling you your query isn't valid. Why do you have two different fields for the ID? Is there a field called "_wf_JOBID" in the table?

                • 5. Re: ExecuteSql Command
                  Jason_Farnsworth

                  I can use

                   

                  ExecuteSQL ( " SELECT CTR_Warning_Type

                  FROM SES

                  WHERE Letter_Age = 1 " ; "..." ; ""    )

                   

                  And it gives me all that meets the Letter_Age = 1 Reqirment

                   

                  If I change it to, I get "?"

                   

                  ExecuteSQL ( " SELECT CTR_Warning_Type

                  FROM SES

                  WHERE Letter_Age = 1 AND _wf_JOBID = ? " ; (This is the JOBID under the SES TABLE)

                  "..." ; "" ; zwf_JOBID (This is the Job ID in the JOB table)  )

                   

                  How do I reference the current JOBID for a filtered result?

                   

                  Jason Farnsworth

                  • 6. Re: ExecuteSql Command
                    Mike_Mitchell

                    Oh, wait a minute. You have to use SQL naming conventions. It should be:

                     

                    JOB_SES."_wf_JOBID"

                     

                    That means you'll have to write it as:

                     

                    "SELECT JOB_SES.CTR_Warning_Type FROM JOB_SES WHERE Letter_Age = 1 AND JOB_SES.\"_wf_JOBID\
                    " = ?"

                     

                    (This is why we don't start field names with underscores. It creates problems for SQL.)   

                    • 7. Re: ExecuteSql Command
                      Mike_Mitchell

                      I’m assuming your JOBID in the SES table is joined to the JOBID in the JOBS table. That being the case, you have to tell SQL that. Again, it doesn’t know anything about the Graph, so you can’t leverage any existing relationships. You can do a join like this:

                       

                           “SELECT SES.CTR_WARNING_Type FROM SES JOIN JOB ON SES.\”_wf_JOBID\” = JOB.zwf_JOBID WHERE SES.Letter_Age = 1”

                      • 8. Re: ExecuteSql Command
                        Jason_Farnsworth

                        Mike,

                         

                        Wow,

                        I have been starting my Primary Keys with __wp_ and my Foreign Keys with _wf_ forever and never really had and issues. I like the convention as it put all my keys are the beginning of the list.

                         

                        I ran into an issues recently linking to a ODBC connection and had to start moving from that convention. I guess I will go through and fix them all now.

                         

                        It works now thank you,

                         

                        Jason Farnsworth

                        • 9. Re: ExecuteSql Command
                          Mike_Mitchell

                          FileMaker is very forgiving with its naming conventions. Other systems, not so much. Like you, I ran into this problem when interacting with outside SQL databases and started being a lot more conservative. Reduced my stress level.  

                           

                          Glad it works.

                          • 10. Re: ExecuteSql Command
                            BruceHerbach

                            The issue is probably the field _wf_JOBID.  SQL doesn't like _ as the first character in a field name.  Try it this way

                             

                             

                            ExecuteSQL ( " SELECT a.\"CTR_Warning_Type"

                            FROM \"SES\" a

                            WHERE a.\Letter_Age\" = ?" AND a. \"_wf_JOBID" = ? " ; "..." ; "" ; 1;  zwf_JOBID )

                             

                            The current job ID can be a field in the table/record that the layout you are onis based on,  or you can put it into a variable and put the variable in the SQL.

                             

                            Your mileage may vary,  I'm not sure I have all of the " and \ in the correct place.. 

                             

                            HTH