3 Replies Latest reply on Jun 21, 2012 3:19 PM by Robert Morton

    How can I manipulate or condition the SQL queries that Tableau send to an ODBC driver?

      I'm using a custom ODBC driver running under Progress DataDirect OpenAccess.


      On Progress Analyst Studio, when I drag-and-drop a DateTime column (UploadDate) onto the Rows area I get an error, and on the details it shows that the program is sending this query:

       

      SELECT {fn FLOOR(EXTRACT(YEAR FROM UploadDate))}
      FROM MyTable
      GROUP BY {fn FLOOR(EXTRACT(YEAR FROM UploadDate))}

       

      but when I right click on "UploadDate" and select "All Values" instead of "Year" it works as it sends the query below:

       

      SELECT UploadDate
      FROM MyTable
      GROUP BY UploadDate

       

      Also, similar issue with "WHEN NOT" clause:

       

      SELECT (CASE WHEN (Name = 'myname') THEN 1
      WHEN NOT (Name = 'myname') THEN 0
      ELSE NULL END),
      Name
      FROM MyTable


      What I would like to do is to indicate Analyst Studio that my driver does not support functions (or WHEN NOT), how can I do this?
      I had a look at the article:

      http://kb.tableausoftware.com/articles/knowledgebase/customizing-odbc-connections

       

      When it describes how you can customize the settings of your ODBC connection features, and I tried with:

       

      CAP_ODBC_BIND_FORCE_DATE_AS_CHAR = false
      CAP_ODBC_BIND_FORCE_DATETIME_AS_CHAR = false
      SQL_TIMEDATE_FUNCTIONS
      (Integer bitmask. Defines which SQL scalar date / time functions are supported)

       

      But the first two didn't work and for the third I couldn't find any information about how to define the submask value.

      Any suggestion/idea is more than welcome.

       

       

      Many thanks in advance!