Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to copy the output data and use it to execute a SQL query in Alteryx?

Highlighted
7 - Meteor

I need to run a SQL query using the data that I have obtained from an output file. How can this be handled?

Highlighted
Alteryx Certified Partner

Hi @Santy to get a better context on what you are looking to achieve, are you looking to build something where you have some values in excel file (say ID's) that need to be passed into a SQL's WHERE clause? If yes, then the approach would look something like what I have included in the attached solution. Note: I haven't included any database connections in it, so you will have to connect it with your environment.

 

AbhilashR_2-1590011462198.png

 

I have included two different approaches addressing different scenario

Scenario 1: if you are using an Oracle database and have more than 1000 ID's, you will have to pass ID's in batches of 1000 (this is an Oracle limitation)

Scenario 2: if you are using simple SQL for a small universe of ID's, a simple Dynamic Input tool will get you results.

 

Please let us know if this isn't what you were looking for.

 

Highlighted
7 - Meteor

Hi @AbhilashR,

 

Thanks. To give you more specifics, I have an output excel which has a list of values in one column. I have 2 options here. Either pick one row at a time and parse the values in a where clause of SQL or combine all of these values with a separator and parse it in where clause of SQL.

 

I feel combining them would be more appropriate. Is there a way I can transpose/concatenate  all the rows into one single cell value and use it as an input for the where clause in SQL? Let me know.

 

In the meantime, Let me look at your solution in detail.

 

Regards,

Santosh

Highlighted
Alteryx Certified Partner

Hi @Santy, I concur with your thought. Transpose/Concatenate is a more efficient way of fetching data in this case.

 

Summarize tool allows you to transpose and concatenate columns and lets you define the delimiters. Going back to the sample solution I previously posted, the summarize tool concatenates the ID's with comma as a delimiter. I have included a screenshot of the Summarize configuration below:

AbhilashR_0-1590076800856.png

 

 

Highlighted
7 - Meteor

Thanks @AbhilashR.

 

I used a different way as below and its working fine for my use case.

 

Santy_0-1590091619935.png

 

Labels