ExecuteSQL - Field cannot be found

[Pat Fellows]

Hello, 

I am extremely new to working with SQL, so i am currently fumbling my way through achieving my required outcomes. 

To explain further explain my issue, i created a table/layout on one of my databases that i could use to practice and visualize the outcomes of ExecuteSQL. 

I type into my "query" field and that drives a separate ExecuteSQL calculation. 

I have been able to achieve the required outcome in this field, however when i transfer the query directly into a ExecuteSQL calculation i get errors. These relate to the field in financials called "Cost Code"

If i attempt the same calculation without it, i can run it without an issue. However what confuses me is it will work when input through a query field. 

Query field;

SELECT SUM(Total)
FROM financials
Where "Cost Code" = 'FA' and EID = '982 000147908607'

Please find attached images highlighting and showing everything relevant. 

 

Kind regards
Pat

 

[Kevin Frank]

Hi Pat,

Since the Cost Code field name contains a space you need to wrap it in double quotes as you have done, but... furthermore... you need to escape the double quotes by prepending a backslash character.

ExecuteSQL ( 

"SELECT SUM(Total)
FROM financials
Where \"Cost Code\" = 'FA' and EID = '982 000147908607' " 

; "" ; "" ) 

Also, I would take advantage of dynamic parameters and construct the query like so:

ExecuteSQL ( 

"SELECT SUM(Total)
FROM financials
Where \"Cost Code\" = ? and EID = ? " 

; "" ; "" ; "FA" ; "982 000147908607" ) 

Hope this helps,

Kevin

[Pat Fellows]

Thank you Kevin,  that allows me to accomplish exactly what i needed to without creating a ridiculous number of relationships. 
I feel silly to have never considered the space, live and learn. 

 

Kind regards
Pat

[Kris M]

I'm really surprised that Total is NOT a SQL reserved word