3 Replies Latest reply on Aug 2, 2012 10:51 AM by Mike_Mitchell

    ExecuteSQL List Operations?


      Is it possible to use ExecuteSQL to operate on lists?


      See for instance this discussion on selecting distinct values from a list:




      Which says that this construct works for SQL Server 2008:


      select distinct * from (values (1), (1), (1), (2), (5), (1), (6)) as X(a)


      I have tried doing the above as follows but it doesn't work:

      ExecuteSQL( "select distinct * from (values (1), (1), (1), (2), (5), (1), (6)) as X(a)"; ""; "")

        • 1. Re: ExecuteSQL List Operations?

          Hi Bruce,

          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.




          • 2. Re: ExecuteSQL List Operations?

            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.


            Go Figure


            Bruce, did you try using a plug-in's SQL? BaseElements, SQLRunner, ScriptMaster, et al.




            • 3. Re: ExecuteSQL List Operations?

              Dumb question: I haven't tried it, but what about IN? It appears in the ODBC guide.