Query where both table and field names contain spaces

[kfeete]

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)

[comment]

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.

 

 

[kfeete]

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 August 19, 2020 by kfeete

[kfeete]

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 August 19, 2020 by kfeete

[comment]

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 August 19, 2020 by comment

[kfeete]

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!

[comment]

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 August 21, 2020 by comment

[kfeete]

Thank you! I was hoping you meant something along these lines, that will make my life a whole lot simpler.