SQL on Field Names Containing Spaces

[Chong-Yee]

Hi, Everyone,

Is there any way to use FileMaker's Internal SQL on a solution, where the names of the fields contain spaces?

Statements like:

SELECT Last Name from People WHERE Team Role = 'Judge'

or even

SELECT zzID from People WHERE Last Name = 'Tan'

fails spectacularly - I think because the SELECT clause expects a comma delimited list of field names. Here, the field names in my solution use spaces (for legibility - and FM does not complain when I do that).

I have tried SELECT 'Last Name' but that only gives the literal in every row.

The FM ODBC/JDBC Guide is not much help. All it says on page 47 (Field Names) is

"The most common expression is a simple field name, such as calc or Sales_Data.Invoice_ID." (thanks!)

It should't make any difference, but I am using the BaseElement plugin 1.2.1 to send the query to FileMaker.

Any help would be gratefully appreciated. Thank you in advance.

Regards,

Chong-Yee

[RalphL]

I hope you have figured this out by now. If not here are 2 methods:

Use the Quote function

"SELECT " & Quote( Last Name) & " from People WHERE " & Quote( Team Role ) & " = 'Judge'"

or use the Escape ( \ ) with quote

"SELECT \"Last Name\" from People WHERE \"Team Role\" = 'Judge'"

[Chong-Yee]

I hope you have figured this out by now. If not here are 2 methods:

Use the Quote function

"SELECT " & Quote( Last Name) & " from People WHERE " & Quote( Team Role ) & " = 'Judge'"

or use the Escape ( ) with quote

"SELECT "Last Name" from People WHERE "Team Role" = 'Judge'"

Thank you very much for this, Ralph!

[Scott Howard]

In FileMaker 12 I could not get the Quote() method to work with with field name with spaces, but the escaped quotes worked fine:

"SELECT "Last Name" from People WHERE "Team Role" = 'Judge'"

[Chong-Yee]

In FileMaker 12 I could not get the Quote() method to work with with field name with spaces, but the escaped quotes worked fine:

"SELECT "Last Name" from People WHERE "Team Role" = 'Judge'"

Thanks very much for the update, Scott. I'm on 11 at the moment and until the problems with speed with 12 are resolved, unlikely to upgrade. But it's useful to know that the ExecuteSQL script step woks somewhat differently from the plugins.

[PeterWindle]

I have just started learning SQL, in order to use the quote function in this example, you need to :

 

"SELECT " & Quote( "Last Name") & " from People WHERE " & Quote( Team Role ) & " = 'Judge'"

seems odd that you need to quote the quote, but I guess it's because quote normally looks for a filemaker field, whereas we are quoting a literal here