Forum Discussion

Tom's avatar
Tom
New Contributor III
8 months ago

SQL Query to External Table

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

Extenders
  • DanielWillis's avatar
    DanielWillis
    Valued Contributor
    8 months 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?

     

    • Tom's avatar
      Tom
      New Contributor III
      8 months 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'.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    8 months 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.

    • Tom's avatar
      Tom
      New Contributor III
      8 months ago

      Are you using a BR ? or a Data Adapter ?  

  • Krishna's avatar
    Krishna
    Valued Contributor
    8 months 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"

     

     

  • Tom's avatar
    Tom
    New Contributor III
    8 months 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'.

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    8 months 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.