Using custom SQL in a Tableau data connection can cause unexpected errors.
There are many different flavours of SQL, and they don’t share the same syntax. This is generally the cause of any errors.
Even MS Access, which has the same SQL as Tableau, can have problems connecting Access queries to Tableau.
Tableau uses the MS Jet data engine which has slightly different syntax to SQL used in SQL server or the SQL used in Oracle.
Syntax errors in Tableau custom SQL due to MS Jet SQL
Tableau custom SQL Date function differences
For example, when using the DATEADD function, the syntax differs to other SQL variations.
This link from Microsoft could also help with writing the datepart intervals.
Write a CASE statement in Tableau custom SQL
MS Jet SQL also does not recognise the CASE statement.
Instead use an alternative, such as nested IIF statements. This post I found suggesting CASE alternatives, written back in 2006, suggests CHOOSE.
In the end it all depends on how you use the CASE statement.
Write CAST or CONVERT in Tableau custom SQL
It’s also not possible to CAST or CONVERT with MS Jet SQL – there are alternative functions existing for casting and converting, for example CDATE([field]) converts to a date and CINT([field]) converts to an integer. This link supplies a list of cast/convert alternatives using the C… functions.
GETDATE in Tableau custom SQL
Another big difference is GETDATE(). In MS Jet SQL this is NOW().
Familiarise with MS Jet SQL
Once familiar with MS Jet SQL, to import using custom SQL is simple.
However, when comparing to “standard” SQL the syntax could cause problems.
In addition, the error messages thrown up are generally quite unhelpful to identify where the syntax is incorrect.
The Custom SQL is actually a very useful part of the Tableau toolkit once you understand it. If you regularly use text files then sometimes custom SQL is the best way to restructure your data to produce your dashboard.