Custom SQL data connections in Tableau

//

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.

With DATEADD the syntax also differs between the Custom SQL connection and the Tableau calculated fields.

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.

8 thoughts on “Custom SQL data connections in Tableau”

  1. I am not able to open csv and other text files using different delimiters like “^”.
    Is this also a limitation or is there a way that i have not looked at as of yet?

    Reply
    • You should be able to – assuming you’re using Tableau 9. When connecting to data, select csv, then edit the field separator in the Data Source area. It’s a cog symbol in the grey pill with the data source name where you can edit. Let me know if you can’t find it.

      Reply
  2. Pingback: Data Visualization – batish
  3. I tried to use CStr to convert number to string in Tableau’s custom SQL, but it didn’t work.
    The error message : No authorized routine named “CStr” of type FUNCTION having compatible arguments was found. SQLSTATE = 42284.

    Can anyone tell me what’s the correct syntax for converting a number to a string?

    Thank you.

    Reply
    • Many SQL functions don’t work when using custom sql. I suggest just pulling it into Tableau as a number and then change the data type within Tableau. You’re able to change the field type of all dimensions by right clicking on the field.

      Note the field by default is likely to be a measure if it’s numeric – just drag it up to the dimensions area to change it a dimension.

      Reply
    • Perhaps IF isn’t supported my MS Jet. I think IIF could be – at least that is supported in the WHERE clause of an access query. Depending on where you have the IF statement may drive the solution – for example perhaps the IF could be applied on the reporting layer, within Tableau.

      Reply
  4. Pingback: Getting Started with Tableau Public - Aspired Techie

Leave a Reply to awatson Cancel reply