CODEWITHSUNDEEP
mysqltableau-api
Jason Hahn
Jason Hahn

Reputation: 139

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

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;

Upvotes: 3

Views: 14856

Answers (3)

Abirami_Jothi
Abirami_Jothi

Reputation: 29

I came across a similar situation with custom SQL query. I had a dashboard developed with custom SQL query connecting to database with a program ID filter - "SQL Query where program id = 222". I needed to replicate the same dashboard for another program "SQL Query where program id = 333".The query didn't work initially with refreshing the data source.

Solution - if you are working with extract change the connection back to 'live', create a new extract.

Upvotes: 0

Jason Hahn
Jason Hahn

Reputation: 139

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."

Upvotes: 10

vizyourdata
vizyourdata

Reputation: 1444

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

Upvotes: 1

Related Questions