Why is my Custom SQL query (which works fine outside of Tableau) failing in Tableau?

Question

My query below runs fine in mysql client (Heidi) but errors out in Tableau. I've looked here and in the Tableau Community site and the only suggestion I see is to take out semicolons. I've tried that to no avail. I am connected just fine to my database through Tableau - I can see the tables and other queries run without a problem. Any ideas on what might be the problem here? I'm running Tableau 8.2. Thanks!

SET @sql=NULL;

SELECT
Group_Concat(Distinct CONCAT(
'MAX(IF(wsd.cid = ''', wc.cid, ''', wsd.data, NULL)) AS    ''',wc.name,'',''''))
INTO @sql
FROM webform_component wc
WHERE wc.nid = 107;

SET @sql = Concat('SELECT wsd.sid,',@sql,'
FROM webform_submitted_data wsd
LEFT Join webform_component AS wc ON wsd.cid=wc.cid
WHERE wsd.nid = 107 AND wsd.sid >= 14967
GROUP BY wsd.sid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; 

Solution 1

It turns out that Tableau SQL does not support this type of query when connected to a mySQL database. The recommendation I received was to to try to reformat this as a nested subquery.

This is the answer I received from a Program Manager at Tableau when I asked if using the above query was possible:

"You can't. It should be a single query that returns a result set. Tableau will wrap the custom SQL query as a subquery.If your SQL can't be treated that way, you will get errors.

We support this structure on data sources that we support "initial SQL". For example Teradata, Aster...

It allows you to run any SQL upfront, create temp tables etc. hence called initial SQL.

Then you can write a query as part of connection which will be evaluated after "initial SQL" and take advantage of the objects created in the initial SQL step."

Solution 2

I had this problem with custom queries as well. I could connect to the db and use the gui to bring data in but I couldn't write a custom query.

Downloading the mysql drivers was enough to fix it for me.

I have also read that you should download versions 3.51 and 5.X and use the 32-bit versions of each even if you are running 64-bit Tableau

Comments

[Alex Blakemore]

If you would like to encourage Tableau to add the Initial SQL feature for MySQL, like they do for other databases, upvote the following idea on their site community.tableau.com/ideas/4491