Pass-Through Functions (RAWSQL)
These RAWSQL pass-through functions can be used to send SQL expressions directly to the database, without first being interpreted by Tableau. If you have custom database functions that Tableau doesn’t know about, you can use these pass-through functions to call these custom functions.
Your database usually will not understand the field names that are shown in Tableau. Because Tableau does not interpret the SQL expressions you include in the pass-through functions, using the Tableau field names in your expression may cause errors. You can use a substitution syntax to insert the correct field name or expression for a Tableau calculation into pass-through SQL. For example, if you had a function that computed the median of a set of values, you could call that function on the Tableau column [Sales] like this:
RAWSQLAGG_REAL(“MEDIAN(%1)”, [Sales])
Because Tableau does not interpret the expression, you must define the aggregation. You can use the RAWSQLAGG functions described below when you are using aggregated expressions.
RAWSQL pass-through functions will not work with published data sources or with Tableau extracts.
These functions may return different results starting in Tableau Desktop 8.2 than they did in earlier versions of Tableau Desktop. This is because Tableau now uses ODBC for pass-through functions instead of OLE DB. ODBC truncates when returning real values as integer; OLE DB rounds when returning real values as integer.
RAWSQL Functions
The following RAWSQL functions are available in Tableau.
RAWSQL_BOOL(“sql_expr”, [arg1], …[argN])
Returns a Boolean result from a given SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values.
Example
In the example, %1 is equal to [Sales] and %2 is equal to [Profit].
RAWSQL_BOOL("%1 > %2", [Sales], [Profit])
RAWSQL_DATE(“sql_expr”, [arg1], …[argN])
Returns a Date result from a given SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values.
Example
In this example, %1 is equal to [Order Date].
RAWSQL_DATE(“%1”, [Order
Date])
RAWSQL_DATETIME(“sql_expr”, [arg1], …[argN])
Returns a Date and Time result from a given SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Delivery Date].
Example
RAWSQL_DATETIME("%1", [Order Date])
RAWSQL_INT(“sql_expr”, [arg1], …[argN])
Returns an integer result from a given SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Sales].
Example
RAWSQL_INT(“500
+ %1”, [Sales])
RAWSQL_REAL(“sql_expr”, [arg1], …[argN])
Returns a numeric result from a given SQL expression that is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Sales]
Example
RAWSQL_REAL(“-123.98 * %1”, [Sales])
RAWSQL_SPATIAL
Returns a Spatial from a given SQL expression that is passed directly to the underlying data source. Use %n in the SQL expression as a substitution syntax for database values.
Example
In this example, %1 is equal to [Geometry].
RAWSQL_SPATIAL("%1", [Geometry])
RAWSQL_STR(“sql_expr”, [arg1], …[argN])
Returns a string from a given SQL expression that is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Customer Name].
Example
RAWSQL_STR(“%1”, [Customer Name])
RAWSQLAGG_BOOL(“sql_expr”, [arg1], …[argN])
Returns a Boolean result from a given aggregate SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values.
Example
In the example, %1 is equal to [Sales] and %2 is equal to [Profit].
RAWSQLAGG_BOOL(“SUM( %1) >SUM( %2)”, [Sales], [Profit])
RAWSQLAGG_DATE(“sql_expr”, [arg1], …[argN])
Returns a Date result from a given aggregate SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Order Date].
Example
RAWSQLAGG_DATE(“MAX(%1)”,
[Order Date])
RAWSQLAGG_DATETIME(“sql_expr”, [arg1], …[argN])
Returns a Date and Time result from a given aggregate SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Delivery Date].
Example
RAWSQLAGG_DATETIME(“MIN(%1)”, [Delivery Date])
RAWSQLAGG_INT(“sql_expr”, [arg1,] …[argN])
Returns an integer result from a given aggregate SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Sales].
Example
RAWSQLAGG_INT(“500
+ SUM(%1)”, [Sales])
RAWSQLAGG_REAL(“sql_expr”, [arg1,] …[argN])
Returns a numeric result from a given aggregate SQL expression that is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Sales]
Example
RAWSQLAGG_REAL(“SUM( %1)”, [Sales])
RAWSQLAGG_STR(“sql_expr”, [arg1,] …[argN])
Returns a string from a given aggregate SQL expression that is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Discount].
Example
RAWSQLAGG_STR(“AVG(%1)”,
[Discount])