04-18-2024 09:31 AM
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.
04-18-2024 11:52 AM
04-18-2024 09:44 AM
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?
04-18-2024 11:41 AM
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'.
04-18-2024 10:11 AM
#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.
04-18-2024 11:40 AM
Are you using a BR ? or a Data Adapter ?
04-18-2024 11:42 AM
@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"
04-18-2024 11:43 AM
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'.
04-18-2024 11:47 AM
We are on version 6.8 and upgrading soon to 7.4
04-18-2024 11:47 AM
@Tom - Could you please try your query in below format
"SELECT ""Column Name"" FROM ""Table Name"" WHERE ""Column name"" = Value"
04-18-2024 11:52 AM
04-19-2024 08:17 AM - edited 04-19-2024 08:18 AM
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.