And you can use aliases, as in: SELECT c.fName, c.lName FROM Contacts c The irony is that the first use of SQL in Filemaker, with the interaction in 4.1 of ODBC, we did indeed have a fairly tradition, Excel-like interface for building the queries. That was for the Execute SQL script step, which is still useful. John Weinshel On 4/3/15, 1:56 PM, "BEVERLY VOTH" <[log in to unmask]> wrote: >you can put 'literals' into your query and UNION the results, or just >concat in your final result: > >Let ( >[ _header = "name,phone,email" >; _query = "SElECT name,phone,email FROM ____ ..." >; _result = ExecuteSQL ( _query, "", "" ) >]; substitute (_header; "," ; char(9) ) & ¶ & _result >) > >my first row "_header" is comma separated literals or as you say, get >from the function - I just find it easier to hard-code. Because this >"row" is comma separated, I need to exchange the comma with a tab >Character (or whatever delimiter is use in the eSQL. then because the row >will appear before the eSQL result, add a return. Final append the result. > >beverly > > >On Apr 3, 2015, at 4:11 PM, Brad Lowry <[log in to unmask]> wrote: > >> Hi All, >> >> I haven't posted in a million years. >> >> (apologies ahead of time if I interchange column/field/attribute and or >>record/row an so on) >> >> I'm late to the ExecuteSQL party, but I've been using SQL for years in >>MySQL, Oracle, Solid, through SmartPill and even MSSQL. Of course, SQL >>is a Language (the 'L' in SQL). not a product so I am thrilled to be >>able to use it out of the box in FileMaker. >> >> However, every Query Tool I have ever used -- SQL Developer (oracle), >>SQLpro (mac), phpMyAdmin (MySQL on web), even as far back as TOAD! -- >>they all had user interface where the returned data is (can be) returned >>with the first row as column names. >> >> I don't see this in FileMaker ExecuteSQL(). >> >> Thanks to Andrew Duncan >>(http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemas >>ystem-tables/) I know that if I absolutely had to, I could collect the >>fieldnames for a particular table. I could even cobble together such a >>first row -- but only where the query was SELECT * -- unless there are >>more things to be done with those virtual tables. >> >> Further, even though when using SQL Developer I am able see the first >>row as column names (and export to csv etc), when I access such a query >>within PHP for instance that 'first row' is not returned as such. >>However, and more importantly, each attribute name is available within >>the return set (resource) with a foreach loop or whatever. >> >> The biggest drawback is column name aliasing. I just tried this query >>(and it worked) before I began writing: >> ======== >> SELECT >> id as hello >> , name as world >> , title >> FROM option_group >> WHERE 1 = 1 >> OFFSET 2 ROWS >> FETCH FIRST 5 ROWS ONLY >> ======== >> >> Of course, this is a silly example. But imagine writing a report for >>the Staff Directory: >> SELECT TRIM(last) || ', ' || TRIM(first) as "Name" FROM staff_directory >>ORDER BY last, first >> >> Wouldn't it be much easier to build a report using the result if you >>were able to access the ColumnNames of the result as *part* or the >>result? >> >> There are workarounds -- there are always workarounds -- populate >>$$session variables to store your column names before you run the query: >> $$columnname[1] = "Name" >> $$columnname[2] = "Phone" >> $$columnname[3] = "Email" >> And then not use the ' as ' aliasing at all... however, since >>ExecuteSQL *allows* it, it would make sense that it be natively >>accessible. >> >> Is there some 'RETURN_COLUMN_NAMES' argument hidden somewhere? >> >> Thanks in advance. >> >> Regards, >> Brad