KNOWLEDGE BASE

How to pass more than one value in a parameter to be used in custom SQL


Published: 29 Mar 2018
Last Modified Date: 21 Jun 2018

Question

How to pass multiple strings through a parameter in custom SQL. Is there any way to pass comma separated values into a parameter and use it in custom SQL?

 

Environment

  • Tableau Desktop 10.4.0, 10.5

Answer

The query below applies to SQL for Oracle.

select * from tablename where name in (
select regexp_substr(<Parameters.Parameter1>,'[^,]+', 1, level) from dual
connect by regexp_substr(<Parameters.Parameter1>, '[^,]+', 1, level) is not null)


The query below applies to SQL for Microsoft SQL Server.

SELECT *
FROM Test.dbo.tablename a
JOIN (  
(SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),  
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(<Parameters.Parameter1>, Number,  
        CHARINDEX(',', <Parameters.Parameter1> + ',', Number) - Number)))  
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])  
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)  
    WHERE Number <= CONVERT(INT, LEN(<Parameters.Parameter1>))  
        AND SUBSTRING(',' + <Parameters.Parameter1>, Number, 1) = ','  
    ) AS y)) x on a.colname = x.Item

Additional Information

By design, a parameter can take only one value at a time.
Did this article resolve the issue?