The values clause does not appear to be included in the ExecuteSQL grammar.
The "as X(a)" seems to refer the select to treat the value list as a table which would seem to violate the
intent of the statement found in the functions reference "ExecuteSQL cannot be used with SQL statements that modify data or the database schema (such as the Insert Into or Delete Table commands).". Thinking that a virtual table, scratch table, or what ever term you prefer, is db schema. One of the variants in the blog thread you refer to offered a "select element union select element" scheme which i was able to build in the data viewer. However, no dice so either the select statement in filemaker requires a "from clause" or a "union clause" is not supported.
Also I noticed that this construct is referred to as an enhancement implemented only in T-SQL (MsSQL) so it would seem strange to include it in the ExecuteSQL implementation since only one of the SQL languages supports this. Also the values clause has it's origins in the insert statement, so may have be excluded for that reason.
I have enjoyed the exercise, but cannot seem to get any variation of the intended behavior from the ExecuteSQL function.
If you have better results, please share. I know you will.
Upon another review of the ODBC JDBC guide, the UNION clause is supported per page 37 and I do not see a requirement that states every select must have a FROM clause. But ExecuteSQL did not like my construct for some reason and would not give a filtered list.
Bruce, did you try using a plug-in's SQL? BaseElements, SQLRunner, ScriptMaster, et al.
Dumb question: I haven't tried it, but what about IN? It appears in the ODBC guide.