SQL Query to External Table

Tom
New Contributor III

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

Select * FROM Table

WHERE "Cost Center" = 'CC_11111111'

;

Tom_0-1713446755671.png

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

1 ACCEPTED SOLUTION
10 REPLIES 10

DanielWillis
Contributor III

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
New Contributor III

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
Honored Contributor

#WorksForMe on 8.1:

JackLacava_0-1713449383288.png

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
New Contributor III

Are you using a BR ? or a Data Adapter ?  

Krishna
Valued Contributor

@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"

 

 

Thanks
Krishna

Tom
New Contributor III

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
New Contributor III

We are on version 6.8 and upgrading soon to 7.4

Krishna
Valued Contributor

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

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

Thanks
Krishna

RobbSalzmann
Valued Contributor

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.