ExecuteSQL Strange Bug

[cgroody]

I am getting a strange result, I am attempting to use the ExecuteSQL command to avoid using multiple Table Occurrences.

However I am stumped when the field I need does not work on ExecuteSQL for an odd reason.

ItemNo and Level are both Indexed Number Fields with the EXACT SAME SETTINGS, however when performing these commands, ItemNo gives me a result, when Level does not. They both have data inside, etc. Any thoughts or ideas why I am receiving a "?" on the second 

ExecuteSQL("SELECT c.ItemNo FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"")

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"")

 

 

[bruceR]

I'm not an ExecuteSQL expert but I see a couple of things. You are not using the parameterized form of the query; and you are supplying to many terms at the end of the query. Parameterized form:

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = ?";"";"";"AA")

[cgroody]

17 minutes ago, BruceR said:

I'm not an ExecuteSQL expert but I see a couple of things. You are not using the parameterized form of the query; and you are supplying to many terms at the end of the query. Parameterized form:

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = ?";"";"";"AA")

I am not the best at this ExecuteSQL syntax either, but it Works for one field but not for another...

also that expression does not work for either field, so something is wrong with that method.

It seems to do with the FieldName being "Level" when I change the field name from Level to Lvl it works. maybe I am breaking a rule in the syntax using a field name Level... seems a bit silly though. I would assume many developers have a field called Level.

[comment]

You will find a list of reserved words on page 34 of the FileMaker 13 SQL Reference document.

I believe it's good practice to quote your field and table names.

[Wim Decorte]

7 hours ago, cgroody said:

maybe I am breaking a rule in the syntax using a field name Level... seems a bit silly though. I would assume many developers have a field called Level.

Nothing silly about it... it is a matter of being aware of what the standards are and as Comment indicated what reserved keywords come with that standard.  FM's documentation is pretty clear.

Another good example for instance is "grant".  Which is a SQL reserved keyword that will bite you if if you have a client that works with grants.

Just always quote your table and field names.  That gets around all these issues without requiring you to change your field or table names.

There are a number of custom functions floating around that will help you with that.  And will also allow you to pass a real field reference to the SQL query that has the added benefit of keeping your query safe for TO or field name changes.