1 Reply Latest reply on Sep 4, 2012 12:16 PM by philmodjunk

    Issue with a SQL statement

    DouglasReid

      Title

      Issue with a SQL statement

      Post

      I have a look up table with a attributes:

      year (number)

      yearCode (text)

      containing data like:

      • 2012 "C"
      • 2013 "D"

      In a script to work out an invoice code based on my clients parameters I look up the yearCode, setting a variable with a SQL statement thus:

      ExecuteSQL ( " select yearCode from YearCodeLookUp where year = ? " ; "" ; "" ; Year ( Get ( CurrentDate ) ) )

      Except I keep getting the result ? back, i.e. an error and I have no idea why.

      Even when I attempt:

      ExecuteSQL ( " select yearCode from YearCodeLookUp where year = ? " ; "" ; "" ; 2012 )

      The only difference with this look up table is although it is on the Relationship Graph, it has no relationships with the other Tables. SQL however ignores relationships in FileMaker Pro so this should not be an issue...

      Any help would be really appreciated.

      Many Thanks!

        • 1. Re: Issue with a SQL statement
          philmodjunk

          Try either renaming your year field to something else like TheYear or use this syntax:

          ExecuteSQL ( " select yearCode from YearCodeLookUp where \"year\" = ? " ; "" ; "" ; Year ( Get ( CurrentDate ) ) )

          I suspect that Year is a reserved word in SQL and needs to be either avoided as a field name or "escaped" with double quotes.