Input SQL Query Based on Another Output

[Jaganmohan]

Hi Team,

I have this problem in Alteryx. I have a list of  Securities from Filter tool (output). I want to use this list of securities  in SQL query in next step of the workflow (where clause). Could you please guide me on  this.

My SQL query needs a list of Securities  in WHERE clause line. I have a list of Securities as a result of previous step in the workflow. Is there a way to use this list in my SQL?

[ChrisTX]

Here's how I do it:

Start with a Text Input tool with fields for Connection and Query.  The Connection field is a pointer to a File Connection (.indbc) file.

Then I use a Select tool to expand the length of the Query field.

Then I use these tools:

Join: the left input is from the Select above.  The right input is the Where clause, like:

Field name: Where Clause for SQL query
Field value:  (S.FISCALYEAR = 2018 and S.DOCUMENTNUMBER IN ('515888','515999')) or (S.FISCALYEAR = 2019 and S.DOCUMENTNUMBER IN ('190111','190222'))

Join on record position (the left and right inputs to the Join will only have one record).

Then I use a Formula tool to update the Query field to this value:

Replace([Query], "#Placeholder_Where_Clause#", [Where Clause for SQL query])

Then I feed that stream into a Dynamic Input in-DB tool, then into a Data Stream Out tool.

Chris

[christine_assaad · Accepted solution]

Hello @Jaganmohan 

You can also use the Dynamic input tool like below.

Step1: Use Dynamic Input to connect to your SQL DB

Step 2: Build your query visually or using SQL Editor with your where clause

Step 3: Go to Modify SQL query option and use Add --> SQL: Update Where Clause. Then configure your SQL to replace your where clause value with whatever your filter tool upstream tool has (in my case a field called ID)

ID field in upstream tool

Final Output

Hope that helps. Cheers!

[Jaganmohan]

Hi christine_assaad,

Thanks for the quick response... actually  i don't want to manually replace the where condition (need to shift the workflow to gallery).

so, Alteryx need to collect the information from previous step (In your case id's) and replace the same ids under where clause.

For example in the first step i received the output 

id

1

6

8

those three ids need to replace in the where condition of next step (sql).

[christine_assaad]

Hi @Jaganmohan 

What I shared in the Text input is just an example but this could be replaced by the output of any upstream tool to make the workflow dynamic. You won't need to change the where clause manually.  You will just need to point it to the field it needs to grab the data from, automatically. Hope that helps. Cheers!