3 weeks ago
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
Solved! Go to Solution.
3 weeks ago
3 weeks ago
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?
3 weeks ago
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'.
3 weeks ago
#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.
3 weeks ago
Are you using a BR ? or a Data Adapter ?
3 weeks ago
@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"
3 weeks ago
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'.
3 weeks ago
We are on version 6.8 and upgrading soon to 7.4
3 weeks ago
@Tom - Could you please try your query in below format
"SELECT ""Column Name"" FROM ""Table Name"" WHERE ""Column name"" = Value"
3 weeks ago
3 weeks ago - last edited 3 weeks ago
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.