Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Input SQL Query Based on Another Output

Jaganmohan
8 - Asteroid

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?

4 REPLIES 4
ChrisTX
15 - Aurora

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.

 

ChrisTX_0-1662580968330.png

 

Then I use these tools:

 

ChrisTX_0-1662582420674.png

 

 

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

Hello @Jaganmohan 

 

You can also use the Dynamic input tool like below.

 

Step1: Use Dynamic Input to connect to your SQL DB

christine_assaad_0-1662584761280.png

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

christine_assaad_1-1662584829199.png

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)

christine_assaad_2-1662584947363.png

ID field in upstream tool

christine_assaad_3-1662585053630.png

Final Output

christine_assaad_4-1662585080568.png

Hope that helps. Cheers!

Jaganmohan
8 - Asteroid

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).

 

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!

Labels