SQL Query to External Table

[Tom]

I tested the following SQL in a data adapter, accessing a external table

Select * FROM Table

WHERE "Cost Center" = 'CC_11111111'

;

Data returned here, data adapter worked fine.

I am using a Business Rule, and can’t seem to get the BR to work if there is a space in the field name of the external table.

'****WORKS****getExternalDataSql.AppendLine("Select * from TABLE Where PC = 'XXXX' " )

*****Error*****getExternalDataSql.AppendLine("Select * from TABLE Where [Cost Center] = 'CC_XXXXXXXX' " )

I have tried every combination single quote, quotes, brackets on the Cost Center field.

Question: Will an external query work in a BR, where the table has spaces in the names ?

Any help or advice will be appreciated.  Thanks  Tom

[DanielWillis]

Fyi square brackets are used in SQL Servernfor this, and double quotes in Oracle (and many others). That doesn't answer your question but might cut down on testing things. What type of DB are you connecting to?

I think you should get more detail on the error in the Error Log on the System tab. Can you check there?

[Tom]

Snowflake DB.

Here is an error when enclosing in []

Error processing Data Management Step 'Load_FDH_Employee_GL_Actuals'. Unable to execute Business Rule 'FDH_To_OS_Refresh'. ERROR [42000] SQL compilation error:
syntax error line 1 at position 45 unexpected 'Center'.

[JackLacava]

#WorksForMe on 8.1:

As @DanielWillis said, it might be a pecularity of the db server you're using, or there might be something else that is funky about your rule.

[Tom]

Are you using a BR ? or a Data Adapter ?  

[Krishna]

@Tom  - 

1. If the connections are working fine Then I would suggest using the like operator vs equal to see if there are any rows returning.

2. Try the query like this instead of *, there could be some columns causing issue while calling in BR. The below is for SQL. If it is Oracle then you have to use ""

"SELECT [Column Name] FROM [Table Name] WHERE [Column Name] = Value"

[Tom]

Snowflake DB, and here is the error I"m getting when running.

Error processing Data Management Step 'Load_FDH_Employee_GL_Actuals'. Unable to execute Business Rule 'FDH_To_OS_Refresh'. ERROR [42000] SQL compilation error:
syntax error line 1 at position 45 unexpected 'Center'.

[Tom]

We are on version 6.8 and upgrading soon to 7.4

[Krishna]

@Tom  - Could you please try your query in below format 

"SELECT ""Column Name"" FROM ""Table Name"" WHERE ""Column name"" = Value"

[JackLacava · Accepted solution]

https://stackoverflow.com/questions/71553042/snowflake-how-to-filter-on-a-field-name-that-contains-s...

[RobbSalzmann]

When the designer of your table unwittingly decides to use spaces in the names of columns and tables, you lose the option to SELECT * and must explicitly call out column names as pointed out by @JackLacava  and others. 
OTOH, it’s generally not good design to SELECT * in code that parses data from a/ table/s.  Column names should always be used, and often renamed with AS for greater code clarity.