Oracle Passing Multiple parameter values into a single bind variable

Question

From Designer, I am connecting to Oracle DB. I have a requirement to pass multiple values into a Bind Variable.

I am using Dynamic Input to Load the sql and using the 'Text to Replace' to pass the parameter values.  When I pass a single value, it works correctly. My requirement is to pass multiple values.

Think of it as trying to pass zip codes as parameter values to determine the number of residents.  Sometimes, I may pass one value or I may pass multiple values.

The below sub query works well in SQL tool,

AND ZipCode IN
(SELECT regexp_substr(:Enter_Zip, '[^,]+', 1, LEVEL) token
FROM dual
CONNECT BY LEVEL <= length(:Enter_Zip) - length(REPLACE(:Enter_Zip, ',', '')) + 1
)

but when executing in Alteryx, it throws our an error error: ORA-00920: invalid relational operator (Tool Id: 20)

Appreciate your inputs and suggestions.

BrandonB · Answer

You could use Replace([Query], "value to be replaced", [Upstream Field to swap value in]). You won't need to use an interface tool for this purpose as you can just load the values into the Text Input tool and then use a formula following for the replacement values. Then if you do want to make it an analytic app you can just use the interface tools to update the Text Input and all of the downstream logic will follow.

dhanemkulas · Answer

Hi Brandon,

Thank you for the response.

What properties are to be used to update the parameter value in the query usig formula .

Is there a demo work flow that I can leverage to test this solution out.

BrandonB · Answer

What if you have the query in a text input tool, make the required updates via a formula tool, and then have the Dynamic Input tool swap out the entire query? Sometimes this can be an easier approach.