Having a dynamic input in SQL to fetch the data from database.

[prakash-shetty]

I am using SQL to fetch the data from database, but I want the criteria to be dynamic For example in below query loan number in the where clause keeps on changing basis on the data from another sql query which is fetch from another server,  I have only read-only access to database. Is there anyway in Alteryx we can use output of 1st sql query get update in where clause of 2nd query.

1st query - select loan_number from database2

2nd query -  select loanId from database where loan_number in (1234444,222111,1212122,78463739)

[ggruccio]

Hi @prakash-shetty,

You can accomplish this with the dynamic input tool.

You would put the feed/results from the first query into this tool and then tell the tool how to modify the SQL.  For instance I have written SQL with 'dummy' store numbers (i.e. 9999) and simply done a find and replace the values with data from a field.

[AbhilashR · Accepted solution]

Hi @prakash-shetty, achieving this functionality in Alteryx is possible. 

1) Your first Input tool would include your base query: select loan_number from database2

2) Include a Summarize tool and Concatenate the loan_number column. Include separator strings if your query downstream requires you to do so:

3) Include a Dynamic Input tool downstream and setup its db connection. Post that, select Modify SQL Query option and set up the relevant parameters.

Note: if using an Oracle db, it has a limit of allowing only 1000 ID's in where clause at each run. You will have to use a Batch Macro instead of Dynamic Input if you are looking to pass more than 1000 ID's. I have included a skeleton solution for you to build upon.

I hope this helps! 

[ggruccio]

@AbhilashR, thank you for a far more comprehensive answer!  I think you are spot on!

[jarrod]

Hi @prakash-shetty ,

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

As the original author, you also have the ability to mark replies as solutions! Going forward, I’d encourage you to identify the solution or solutions that helped you solve your problem, as it's a big help to other community members. Learn more about Accepted Solutions here.

Thank you!

[VarunLakhwani]

Hi, thanks for this, this was really helpful! Especially the heads up on the limit for Oracle DBs!