Jump to content

ExecuteSQL - Field cannot be found


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

Recommended Posts

  • Newbies

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

 

SQL Error 1.PNG

SQL Error 2.PNG

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

This topic is 3012 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.