Using Output of on SQL as an Input to another SQL

[maniruddha]

In my workflow I get two columns from SQL statement in Input Data Tool (1) say, "Customer" and "Street".  Customer has about 1,000 values and "Street" has about 1,000 values.

I want to be able to use these values for customer and street as a second Input Data Tool which has SQL statement

For example, the second SQL statement is

AND Customer IN

(

)

AND Street in

(

)

I want these two AND statements to be populated by values from the result of first Input Data Tool.

Is this something that can be done by Dynamic Input Tool?

Thanks.

[morr-co]

Hello @maniruddha : as you mentioned, the Dynamic Input Tool is your friend! You first have to configure the Input Data Source Template with your query, then you can use the Modify SQL Query section to update specific values, or specific parts of the SQL string.

Dynamic Input Tool 

[maniruddha]

@morr-co How do you do that?  I tried but I couldn't do it.  Can you please show me an example? Thanks

[morr-co]

Hi @maniruddha : I could see a few approaches to this. The Dynamic Input tool only supports one replacement with groupings. Since you have two "in" conditions in the where statement, you could configure the tool to replace both but you will find that it runs very slow (row by row). My recommendation is to create the where condition and then replace the entire string in the Dynamic Input tool. I've attached a sample workflow of how you could build the where statement.

The attached workflow will build the where statement. Below are some sample screenshots of what the tool configuration would look like.

Sample Query Template

Select "Modify SQL Query", then "Replace a Specific String"

Ensure only the where statement is present in the "Text to Replace" and select the sql field for replacement.