My guess is that Tableau simply has no way to generate a legal query with the TDC settings and information reported from the database. What exactly does the error message say?
Other settings to check:
CAP_SELECT_INTO - probably needs to be no
CAP_SELECT_TOP_INTO - probably needs to be no
CAP_QUERY_SUBQUERIES_WITH_TOP - if this is also no, then I think Tableau may have no way to generate the query
What database are you using?
I'm using Apache Drill. The TDC file is provided by the Drill ODBC driver and all the capabilities you listed are set to no in the file.
The exact error message is: "Unable to materialize temporary table".
We raise that error message in a couple of different places in the code, so it's hard to tell exactly what's happening. But as far as I can tell, we simply have no way to generate a query for that filter.
In some cases we are able to show in the UI that we do not support that kind of query, but it's not always easy to tell ahead of time. You could turn off CAP_QUERY_TOP_N as well and that will prevent all TOP queries and I believe we do remove the UI in this case.
I see. I'll have to find a different way to do it.
Appreciate the help!