Attempting to generate a dynamic SQL based on rows from a table

[LarryRippe]

Attempting to generate a dynamic SQL statement from rows in a table.  Each row is a select and I want to generate a single statement with the individual rows unioned together.  How do I go about doing that?

[JarekSkudrzyk]

@LarryRippe have you tried combining them with "UNION" SQL statement - as described here: https://www.ibm.com/docs/en/db2-for-zos/12?topic=statement-combining-result-tables-from-multiple-sel...

sample workflow could look like this:

[apathetichell]

These are each different queries. I believe you are looking to union the results - not the queries.

What you need to do is create a batch macro and have each of this queries run individually and then union the outputs. If you are doing this In-Db you'll need to use Dyanmic Input In-DB and then datastream out.

If you are doing a regular input/dynamic input the outputs will automatically union if you set your Interface Designer to union (based upon either name or position)

the key either way is to use a macro.

[danilang · Accepted solution]

Hi @LarryRippe 

In your specific case, you can just pass all these queries to a dynamic input tool set up to replace the entire template SQL string.  The reason you can do this is that all your queries return the same schema, assume that the hdrident field is the defined the same in all the tables that you're referencing.  The Dynamic Input will automatically union all the results.

Dan    

[LarryRippe]

Dan, I thought that the Dynamic Input would be the way to go also but am having problems getting it to work.  Can you provide a simple example?

Thank you

[LarryRippe]

Jerek

I was able to generate the entire SQL statement based on your example.  Now, how do I execute that statement?

Thank you

[JarekSkudrzyk]

@LarryRippe please take a look at this post:
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Modifying-SQL-Query-using-the-Dynam...

especially the paragraph on "Modify SQL Query:"

I think this should answer your question.

[LarryRippe · Accepted solution]

After doing a little more research on how to use the Dynamic Input tool, I was finally able to get this to work.  The entire goal was to provide a list of jobs that had run in the past 7 days (job completion history is stored in a separate table) and also include the jobs that are still running along with the status and estimated completion time of each (each job/tool has it's own files that needed to be looked at).  Since I was running an SQL as the template in the Dynamic Input tool, I thought that the calculated values from a 'SUM' operation would always be the same but they were not based on the size of the value being returned and I was getting errors.  After quite a bit of analysis, I determined that I had to define the size of the values being returned from the SQL and then it worked (see SQL below).

 

Dynamic Input Data Source Template