Manual Query Input - Capitalization?

Follow

Glenn Hinkley

November 15, 2018 20:49

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

0

• 

  Malinda Jepsen November 15, 2018 20:55

  I had to do something similar and we ended up writing an SSIS package to bring the data into a SQL Server database using SOQL.  I didn't find a way to do it directly in Sisense. I'm hoping I missed something.

  0

  Comment actions Permalink
• 

  Michael Becker November 15, 2018 21:24

  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ł

  0

  Comment actions Permalink
• 

  Tri Anthony Situmorang November 16, 2018 14:43

  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" = false

  Note: 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.

  1

  Comment actions Permalink
• 

  Glenn Hinkley November 16, 2018 19:04

  Is this special to Salesforce or Sisense?

  0

  Comment actions Permalink
• 

  Tri Anthony Situmorang November 16, 2018 20:42

  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.

  0

  Comment actions Permalink
• 

  Michael Becker November 16, 2018 21:16

  Tri Anthony is right, I have not noticed that earlier. Anyway there is no way to influence how Salesforce querry patern looks like. Also Salesforce uses SQLite, so some functions will be different than what MS SQL/MySQL usually use.

  0

  Comment actions Permalink

Please sign in to leave a comment.