Connecting to a Custom SQL Query

For most relational data sources you can connect to a specific query rather than the entire data source. Often this can be useful when you know exactly the information you need and you understand how to write SQL queries.

1. After connecting to your data, double-click the New Custom SQL option on the Data Source page.
   
2. Type or paste the query into the text box.
   
3. When finished, click OK.

When you finish the connection, only the relevant fields display in the Data pane.

If your SQL query references duplicate columns, you may get errors when trying to use one of the columns in Tableau. This will happen even if the query is valid. For example, consider the following query:

SELECT * from authors, titleauthor where authors.au_id = titleauthor.au_id

The query is valid, but the au_id field is ambiguous because it exists in both the “authors” table and the “titleauthor” table. Tableau will connect to the query but you will get an error anytime you try to use the au_id field. That’s because Tableau doesn’t know which table you are referring to.

To edit a custom SQL query

1. On the data source page, in the join area, hover over the custom SQL table until the edit button displays.
2. Click the edit button.
3. In the dialog box, edit the custom SQL query.

Using Parameters in a Custom SQL Query

You can use parameters in a custom SQL query statement to replace a constant value with a dynamic value. You can then update the parameter in the workbook to modify the connection. For example, you may connect to a custom SQL query that provides web traffic data for a particular page that is specified by a pageID. Instead of using a constant value for the pageID value in the SQL query, you can insert a parameter. Then after finishing the connection, you can show a parameter control in the workbook. Use the parameter control to switch out the pageID and pull in data for each page of interest without having to edit or duplicate the connection.

You can create a parameter directly from the Custom SQL dialog box or use any parameters that are part of the workbook. If you create a new parameter, it becomes available for use in the workbook just like any other parameter. See Parameters to learn more.

To add a parameter to a custom SQL query

1. On the data source page, in the join area, hover over the table until the edit icon displays, and then click the edit button.
2. At the bottom of the dialog box, click Insert Parameter.
3. Select a constant value in the SQL statement and then, from the Insert Parameter drop-down menu select the parameter you want to use instead. If you have not created a parameter yet, select Create a new parameter. Follow the instructions in Creating Parameters to create a parameter.

Note: Parameters can only replace literal values. They cannot replace expressions or identifiers such as table names.

The workbook in the example below connects to a Custom SQL query that returns all orders with that are marked as Urgent priority. In the SQL statement, the order priority is the constant value. If you want to change the connection to see the High priority orders, you would have to edit the data source.

Instead of creating and maintaining many variations of the same query, you can replace the constant order priority value with a parameter. The parameter should contain all of the possible values for Order Priority.

After you create a parameter, you can insert it into the SQL statement to replace the constant value.

After you finish editing the connection, the new parameter is listed in the Parameters area at the bottom of the Data pane and the parameter control displays on the right side of the view. As you select different values, the connection updates.

Note: If you are using an extract, you must refresh the extract in order to reflect changes to the parameter. Publishing a data source that uses Custom SQL parameters includes the parameters. The parameters are transferred to any workbooks that connect to the data source.