3 Replies Latest reply on Oct 4, 2012 9:54 AM by beverly

    Whats wrong with this ODBC import query?

    Oliver_Reid

      Query

       

      SELECT "zips"."a_rec_id", "zips"."aa_county_id", "zips"."b_city", "zips"."County Name", "zips"."County Population", "zips"."State Abbreviation", "zips"."State Name", "zips"."Zip Code"

      FROM "zips"

      WHERE "zips"."State Abbreviation" IN (CT,NY, NJ)

       

       

      Error

       

       

       

      ODBC Error: [FileMaker][FileMaker] FQL0007/(1:235): The column named "CT" does not exist in any table in the column reference's scope.

       

       

      What is the correct typography for using IN with a literal list?

        • 1. Re: Whats wrong with this ODBC import query?
          beverly

          IN ( 'CT','NY','NJ' )

           

          only numbers are not quoted.

           

          SELECT "zips"."a_rec_id", "zips"."aa_county_id", "zips"."b_city", "zips"."County Name", "zips"."County Population", "zips"."State Abbreviation", "zips"."State Name", "zips"."Zip Code"

           

          FROM "zips"

           

          WHERE "zips"."State Abbreviation"  IN (CT,NY, NJ)

           

          • 2. Re: Whats wrong with this ODBC import query?
            Oliver_Reid

            So fields are double quoted (whne needed) and strings are single quoted?

             

            Is this standard for SQL or just FMQL?

            • 3. Re: Whats wrong with this ODBC import query?
              beverly

              Standard SQL, AFAIK. Since I mostly control the SQL and FileMaker creation, the field and tables names don't need to be double-quoted.

               

              In your queries, only fields & tables that would not conform would need to be quoted for ExecuteSQL(). Examples:

               

                   table."Last Name", table.lastName, table.name_last, "my table".lastName, table."_kp_contact_ID", "2nd_table"."odd field Name|"

               

              But you are asking for "literals" in the WHERE clause, so they need to be single quoted. If you have a single quote in your search term, you double the singleQuote:

                   WHERE "my table".name_last = 'O''Brian' // single quote escaped with another single quote

                        OR "my table".name_last = 'OBrian' // in case there is no apostrophe

               

              Beverly

              p.s. one more time for those who missed the FMP help topic (it's been around awhile!) on naming

              http://www.filemaker.com/12help/html/create_db.8.9.html