Jump to content

SQL on Field Names Containing Spaces


This topic is 3073 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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'"

Link to comment
Share on other sites

  • 1 month later...

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!

Link to comment
Share on other sites

  • 4 months later...

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.

Link to comment
Share on other sites

  • 3 years later...
  • Newbies

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

  • Like 1
Link to comment
Share on other sites

This topic is 3073 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.