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-schemasystem-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