Passing workflow constant to SQL Query - using IN condition for multiple items

[terrence10]

I am passing workflow constants in my SQL query input tool like below:

 

I want to be able to query multiple items - e.g Strategy in ('Forwards', 'Down200')

When I enter in my workflow constants like this with single quotes, the query errors because the quotes - it looks like Alteryx adds a single quote around the whole input string - and SQL cannot interpret this

Log:

If I submit my inputs without the single quote, the query completes, but no records are returned:

Log:

The query works if I only pass one value - e.g. Down200 with no quotes.

I want to be able to pass one value as a constant - e.g. ('Down200') or two values ('Down200', 'Forwards')

How do I structure my SQL query input tool and/or Workflow inputs in order to query multiple items in an IN condition?

Thanks!

[davidskaife · Accepted solution]

Hi @terrence10 

I know its not the ideal solution but could you not enter Down200', 'Forwards as a temporary measure?

Also found this which may be of use - https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Passing-the-workflow-constant-...

[terrence10]

Thanks @davidskaife !

Not ideal to have the user format the input as Down200', 'Forwards when running the workflow - but it works!

I think the situations where we would run multiple inputs will be rare so I've accepted this solution for now. This is good to know that if we follow this input syntax - we will be able to run multiple inputs

Thank you!

[davidskaife]

You're welcome. Yeah it's not ideal from a users perspective!

A query - are you providing this to the user as an App on the Gallery? Rather than using constants could you not use the Interface tools to fill out the required inputs, that may be a better workaround?

[terrence10]

I am running this workflow in both places - sometimes in App, sometimes in Gallery.

Not too sure which interface tools might work for this case @DavidSk  ? 

You mean like maybe have an action tool that intakes 'Down200', 'Forwards' and replaces this in a query?

Thanks again!

[davidskaife]

Hi @terrence10 

Something like that yeah. As an example using a Text Box tool you could ask the user to enter Down200 and Forwards and the Action tool will update the query - take a look at the Dynamic Input tool, if you're not already, as that has a section for modifying SQL.

It would likely take a bit of trial and error to get it working correctly.

[terrence10]

Got it, thanks! I haven't used the Dynamic input tool much, so will give that a try - to "Modify SQLQuery" with this tool

Thanks again!