Dynamic SQL Statement for Dynamic Input Tool

[andrewplaice]

Hi all,

Let me start by saying I am way over my skis here but I can't help myself... this is what happens when you give a business guy Alteryx and he has one last step to saving 1,200 plus labor hours a year for a routine. 

I need to create a dynamic SQL query (that statement alone makes me chuckle considering where I was 3 months ago).  I have staged the data so all I need to do is combine each of the rows [Formula2] (and I can input into the dynamic input tool.  The catch is that the or statements may fluctuate.  I can have as few as 2 or dozens.  My data looks like this ( I know it's not elegant - still mastering the cleaver before the scalpel):

RecordID	Formula2
1	select [Fun Stuff] where
2	(Statement A)
3	OR
4	(Statement B)
5	OR
6	(Statement C)
7	OR
8	(Statement D)
9	OR
10	(Statement E)
11	OR
12	(Statement F)

I want it to look like this, " select [Fun Stuff] where (Statement A) OR (Statement B) OR (Statement C) OR (Statement D) OR (Statement E) OR (Statement F)"

I can solve this if I have a fixed number of or statements but the variability is throwing me off.

Thanks so much!

[CharlieS · Accepted solution]

---

@andrewplaice wrote:

saving 1,200 plus labor hours a year for a routine. 

---

Daaang, I'm sure the Community can help. 

So what I'm going to suggest is a complete string replacement instead of doing it in pieces. In this case you would prepare the entire SQL statement(s) ahead of time, then replace the entire query text in the Dynamic Input tool. 

You could dynamically combine the statements using a Concatenation function of a Summarize tool and use " OR " as the separator. Once you have the query string in a single field, pass that to the Dynamic Input for a string replacement of the entire placeholder query string. In the attached example, I use a Random Sample tool to grab 3 random statements each time just so it's dynamic. 

Let me know if this could work for you. 

[andrewplaice]

Thanks Charlie!  

The concept will work!  I'll need to stage it a little differently because this creates a "where or" syntax which won't work, but I get the concept.

Cheers!!