Jump to content

Query where both table and field names contain spaces


Recommended Posts

Hi folks.

I'm attempting to run an SQL query that references a table with a space in its name.

ExecuteSQL ("SELECT SUM (\"Quantity removed from inventory\") FROM \"Trade Record Ledger\"
JOIN \"Trade Records\"
ON Trade Record Ledger.ID_Record = "\Trade Records\".\"Trade Record ID\"
WHERE \"ID_Quantity\" = ? AND \"ID_SubRecord\" LIKE ?" AND 
"Trade Records".\"Action Date\" = ?
; "" ; ""; "QUN5", "INV%", "08/12/2020")

This returns a number, text constant, field name, or "(" is expected here - highlighting the beginning of "\Trade Records\" on the third line.

The following, simpler, query executed with no issues:

ExecuteSQL ("SELECT SUM (\"Quantity removed from inventory\") FROM \"Trade Record Ledger\"
WHERE \"ID_Quantity\" = ? AND \"ID_SubRecord\" LIKE ?"
; "" ; ""; "QUN5", "INV%")

My best guess is, ExecuteSQL doesn't like my using escaped double quotes around the table name, but I'm not sure what the proper syntax is, or whether this is even something FM permits as part of ExecuteSQL. Any advice you might have would be appreciated!

 

(other than "go back in time and don't use table names with spaces in them" -- alas, I don't have the budget for yet another time machine)

Link to post
Share on other sites

The \ escape character goes before the " quote character it escapes, not after

After you fix that, you will get more errors due to unescaped quotes and missing closing quote.

I recommend using the Quote() function to do your escaping for you.

Consider also using a custom function for field and table names; this allows you to change the names without breaking your query.

 

 

  • Thanks 1
Link to post
Share on other sites
Posted (edited)

Comment, thank you for such a quick response!

You're right, of course, that double-quote character placement was a mistake. I've been going through so many different variations of the syntax that I typoed and never noticed.

And yes, once that was cleaned up, the missing closing quote popped up.

Here's the code that worked, after the typos were cleaned up.

ExecuteSQL ("SELECT SUM (\"Quantity removed from inventory\") FROM \"Trade Record Ledger\"
JOIN \"Trade Records\"
ON \"Trade Record Ledger.ID_Record\" = \"Trade Records.Trade Record ID\"
WHERE \"ID_Quantity\" = ? AND \"ID_SubRecord\" LIKE ? AND 
\"Trade Records.Action Date\" = ?"
; "" ; ""; "QUN5"; "INV%"; "08/12/2020")

Lesson learned:

If the table name and/or the field name have spaces, wrap the entire pointer in escaped double-quotes. So, 

\"Trade Record Ledger.ID_Record\"

is fine, but 

\"Trade Record Ledger\".ID_Record

is not.

Edited by kfeete
Link to post
Share on other sites
Posted (edited)
3 hours ago, comment said:

I recommend using the Quote() function to do your escaping for you.

It took some wrangling, until I found this comment. The syntax that worked for me was this: (note the double-quote marks inside the Quote function)

ExecuteSQL ("SELECT SUM (" & Quote("Quantity removed from inventory") & ") FROM " & Quote("Trade Record Ledger") & 
"WHERE " & Quote("ID_Quantity") & "= ? AND " & Quote("ID_SubRecord") & "LIKE ?"
; "" ; ""; "QUN5"; "INV%")
Quote

Consider also using a custom function for field and table names; this allows you to change the names without breaking your query.

 I've not really used custom functions, will have to do some reading up on it, thanks!

Now that I'm past the syntax issues, to figure out why my JOIN doesn't actually do what I want it to...

 

PS. This is in FM Advanced 18 on OS X, in case that matters.

Edited by kfeete
Link to post
Share on other sites
3 hours ago, kfeete said:

If the table name and/or the field name have spaces, wrap the entire pointer in escaped double-quotes. So, 


\"Trade Record Ledger.ID_Record\"

is fine, but 


\"Trade Record Ledger\".ID_Record

is not.

I believe you are mistaken about that. You need to quote table name and field name separately.

 

6 hours ago, comment said:

I recommend using the Quote() function to do your escaping for you.

I am afraid you misunderstood me. My suggestion is to construct your SQL query in a text field until it works. Then copy the result of Quote ( YourTextfield ) and paste it into the calculation formula. For example, your query could be:

SELECT SUM ("Quantity removed from inventory") 
FROM "Trade Record Ledger" 
WHERE "ID_Quantity" = ? AND "ID_SubRecord" LIKE ?

and feeding this to the Quote() function will return:

"SELECT SUM (\"Quantity removed from inventory\") ¶FROM \"Trade Record Ledger\" ¶WHERE \"ID_Quantity\" = ? AND \"ID_SubRecord\" LIKE ?"

which can be used as the sqlQuery parameter of ExecuteSQL() as is.

 

Edited by comment
Link to post
Share on other sites
On 8/19/2020 at 4:32 PM, comment said:

My suggestion is to construct your SQL query in a text field until it works.

I'm sorry, I'm really out of my element when it comes to SQL inside FileMaker. When you say to construct the query in a text field, are you referring to the "specify calculation" dialog inside the Script Workspace, or something else entirely?

Thank you again for your help!

Link to post
Share on other sites

No, I am referring to a text field. This could be a global field in a utility table. There you can type your query without worrying about escaping quotes, and test the result using:

ExecuteSQL ( Utility::gTestfield ; "" ; "" { ; arguments... }  )

Once you are satisfied, you can open Data Viewer, copy the result of Quote ( Utility::gTestfield ) and paste it into the ExecuteSQL() formula instead of the field reference.

This method is good for developing any type of code that eventually needs to be used in a "Specify Calculation" dialog - for example, HTML in a web viewer or calculated AppleScript. The Quote() function does all the necessary escaping for you - and if necessary, you can use the Evaluate() function to get back the original, unescaped code.

 

Edited by comment
  • Thanks 1
Link to post
Share on other sites

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.