On Apr 3, 2015, at 5:07 PM, John Weinshel <[log in to unmask]> wrote:

> And you can use aliases, as in:
> 
> SELECT c.fName, c.lName
> FROM Contacts c

But that does not give you the "column names" in the eSQL SELECT statement.

> 
> 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.
> 
The Import script step was (and still is) for the SELECT and it will return the field/column names should you need to match or create a new table.

The Execute SQL script step (not the ExecuteSQL function) is for INSERT, UPDATE & DELETE. and you do specify the column names either manually or with the widget/dialog.

beverly

> 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