Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Updating SQL query WHERE clause with LIKE % operator

mnmemilymnm
8 - Asteroid

I am trying to build an app where someone can use a list box to select multiple parameters for a WHERE clause of a SQL query. I am familiar with using the Dynamic Input tool to update a WHERE IN clause, but WHERE IN doesn't work with LIKE, so I need to update code similar to the following:

 

WHERE [Table].[Column] LIKE ABCD% or [Table].[Column] LIKE EFGH% ...et cetera

 

What I want is for the user to select from a list box the conditions for their query, e.g. the list box includes ABCD, EFGH, IJKL, and MNOP, and the user selects which ones they want. (Let's say they choose ABCD and EFGH.) Then instead of feeding directly into an Action tool, the workflow takes the selections from the list box, does a crosstab, creates a string out of the fields (e.g. "[Table].[Column]. LIKE "+[Field1]+"% or "+"[Table].[Column]. LIKE "+[Field2]+"%", which yields [Table].[Column] LIKE ABCD% or [Table].[Column] LIKE EFGH%), and then uses the Action tool to replace a specific string in the SQL query (the WHERE clause to be precise).

 

I am trying to take this approach, but it seems like I can't take the selections from the list box and create a string with the LIKE operator that then feeds into the SQL query.

 

Does anyone have any strategies for customizing a SQL query WHERE clause with multiple LIKE operators?

2 REPLIES 2
AkimasaKajitani
16 - Nebula
16 - Nebula

Hi @mnmemilymnm ,

 

I tried to rewrite SQL by using the dynamic rename tool, but I can't do it.

So I think it is easy to rewrite the SQL of the Input Data tool directly.

 

I set the List Box tool as is follows.

 

AkimasaKajitani_0-1632365736723.png

 

Start text is as follows.

WHERE (SampleSuperStore_en.Category Like '

 

Separator is as follows.

%') or (SampleSuperStore_en.Category Like '

 

End text is as follow.

%')

 

Action tool setting is as follows.

AkimasaKajitani_1-1632365893959.png

 

The setting of Input tool' SQL is as follows. 

select SampleSuperStore_en.* 
from SampleSuperStore_en 
where SampleSuperStore_en.Category Like 'Office%'

 

This is how I rewrite the where clause.

 

mnmemilymnm
8 - Asteroid

@AkimasaKajitani  Your solution works perfectly! Thank you so much. Before seeing your solution, I ended up reverting to using the List Box to modify a WHERE IN clause and just resigning myself to not being able to use the LIKE operator, but your solution is such a creative combination of both!

Labels