Update SQL query as per User's selection via interface tool(s)

[RajatK]

Hi,

I need some urgent help on this. Would be grateful for any/all responses!

Currently working on this requirement where the SQL query (used to extract data) contains a WHERE clause wherein the ledger is filtered out.

If a single selection is to be made, for example primary ledger. The SQL script would be : " where ledger_name= 'PRIMARY' ". But, if a dual selection is to be made i.e., PRIMARY and SECONDARY the SQL script would be: " where ledger_name in ('PRIMARY', 'SECONDARY').

The challenge is I need to have 3 selections as part of the drop down: Primary, secondary and both. 

How could I achieve - When user selects "both" how would it update the query as ledger_name in ('PRIMARY', 'SECONDARY') and when user makes a selection of just PRIMARY it updates as " where ledger_name= 'PRIMARY' "? Please help!

Thanks!

[martinding · Accepted solution]

Hi @RajatK,

You can write your List Values as Name:Value pairs.

Name is what appears to the User.

Value is what is interpreted by Alteryx.

So in your case:

In the Properties Box, write:

PRIMARY:PRIMARY

SECONDARY:SECONDARY

BOTH:PRIMARY, SECONDARY

And now you should be able to use the IN (     ) clause.

The Docs are here:

https://help.alteryx.com/20223/designer/list-box-tool

[RajatK]

Thank you so much Martin! Appreciate it!

I have one silly question though: When we declare the Name Both as Primary,Secondary, do we need to pass quotes as well or it works as is?
As SQL where clause we would have ' where ledger_name in ('PRIMARY', 'SECONDARY') '.

Thanks again!

[martinding]

Hi @RajatK,

Great question!

From memory, I think passing the values without quotations should work. Having said that, it's always safer to test on the actual data (or some dummy/sample data).

[TheUdbhav]

Hi @RajatK ,
Hopefully you are using Dynamic input tool to replace the query. If yes, please use quotes as part of the query as string.
This will work.
Thanks

@martinding , Great solution..!👍  

[martinding]

Thanks @TheUdbhav for the clarification around using quotation marks!