Manual Query Input - Capitalization?
Using Salesforce, I want to only pull in part of a table to reduce Elasticube size using the manual query input in the table selection. Unfortunately, it appears that Sisense is automatically converting the table name to all capitals, causing it to not find the table in question.
Table name: Order
Auto Query Structure: SELECT * FROM [Order] (works with no issue)
Manual Query Input: SELECT * FROM [Order] (fails with error)
Error:
Error reading from table.
Error Description:
Unknown table '[ORDER]'
Is there any way I can pull only some of the table Order? I do not have authority to make changes to that data source.
Thanks,
Glenn
-
Hi Glenn,
I had a situation where on ODBC preview worked perfectly for simialr case(in a sense that I had to limit number of rows), but then I got errors upon building. Then with support we found a way to edit json config for given type of connector to nominate query patterns. I think this might help in this case, not sure as I have not had problems with Case. I will dig out the location of this file and let you know.
Regards,
Michał
-
Hi Glenn,
The problem is not the auto capitalization. Metadata (table names, column names, etc) are usually not case sensitive for most databases.
To enter manual query on Salesforce, DO NOT use brackets. Instead, use double quotation marks (") around the table and column names.
For example:
SELECT * FROM "Account"
where "isdeleted" = falseNote: I tried not using quotes, it worked too. My guess is that if you don't have any space in the table name, it's fine to not use quotes.
-
It is special to Salesforce. Sisense by default will generate SQL with brackets around the metadata, but that's not necessarily the right syntax for all data sources. You should always follow the syntax of the actual data source whenever you use manual query input because the query is going be run directly against that actual data source.
Please sign in to leave a comment.
Comments
6 comments