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