5 Replies Latest reply on Feb 11, 2014 8:09 AM by opencirrus

    ExecuteSQL problem

    opencirrus

      Can anyone help with the following problem I am having with an ExecuteSQL calculation. I have the destination field set to calculation, and the calculation works fine if I replace the LOWER ID with the actual record that I'm trying to retreive.

       

      This points and returns the correct record as expected:

       

      - ExecuteSQL ( "SELECT assemblyPN FROM Assemblies WHERE ASSEMBLYID = 36" ; "" ; "" ; "")

       

       

      This returns a ? as it is not picking up on the correct LOWER ID (note the field is structure::LOWER ID):

       

      - ExecuteSQL ( "SELECT assemblyPN FROM Assemblies WHERE ASSEMBLYID = LOWER ID" ; "" ; "" ; "")

       

       

      I suspect my issue is with the syntax used in the WHERE ASSEMBLYID = LOWER ID, can anyone check if my syntax is correct please?

       

      (I have tried the following WHERE ASSEMBLYID = 'LOWER ID'

        • 1. Re: ExecuteSQL problem
          coherentkris

          try this.. ExecuteSQL ( "SELECT \"assemblyPN\" FROM \"Assemblies\" WHERE \"ASSEMBLYID\" = ?" ; "" ; "" ; structure::LOWER ID )

          • 2. Re: ExecuteSQL problem
            opencirrus

            Prefect - thank you! Clearly I need to understand the syntax further here (the use of \"xxxxxxx\") and I see I should have used the argument parameter.

             

            This works, thanks again!

            • 3. Re: ExecuteSQL problem
              beverly

              SQL does not know the field/column "LOWER ID" in the table 'assemblyPN' if the you say structure::LOWER ID is the field.

               

              You can "JOIN" or point to the correct field, but must have the "match" somewhere.

              ...

              FROM Assemblies, structure

              WHERE ASSEMBLYID = \"LOWER ID\"

              and \"LOWER ID \" = ?

              ...

               

                • NOTE: the field with a space must be quoted in the SQL.

               

              Since the names of the fields are unique, you don't need to tell SQL which table for which field/column. But you do need to list the tables where these fields/columns are found!!

               

              Which table/layout are you on when you make this query? Perhaps this query needs to be revised based on another  "context" (as FMP knows it)?

               

              Beverly

              • 4. Re: ExecuteSQL problem
                coherentkris

                ExecuteSQL does not like spaces in field names nor does it like it when you use SQL reserved words as field names. The escaping \" eliminates those types of conflicts that produce a ? result.

                • 5. Re: ExecuteSQL problem
                  opencirrus

                  Thanks Beverly, this is answered now. I had got into bad habbits with FM and used spaces in a name that SQL does not like. I had forgoten this and didn't understand the need to use the \"escape\".

                   

                  Your suggestion also helpful.

                   

                  Mike