SQL Query that generates two distinct result tables

Question

Hi. I have a SQL query that when it completes its run in the SQL Editor, there are two different display results. It's because it's two parts with TEMP Tables listing out all of the edits in a defined period and the same TEMP tables lists out all of the Users who made the edits.

Before I drop the SQL query into an Input Tool, I am curious as to how Alteryx will express the two sets of results from the same query. I would need to eventually take the first results and write to a worksheet in Excel and then take results of the second query within the same SQL to worksheet 2. I know how to create multiple worksheets within the same Excel file in Alteryx, but more needing to know which tools I would need to keep the two results from same query separate?

apathetichell · Answer

So It's probably doable - it just probably requires a rearchitecting between your current process and how you'd do it in Alteryx. I run a union of 12 tables via dynamic input in-db - perform an aggregation and then spit out the results which kind of sounds like what you are doing. In my case I keep my table names in a text input - use a summarize tool - feed it in - aggregate - and spit out back to canvas.

JPSeagull · Answer

@apathetichell - I suspect it's not possible for Alteryx to see that there is SQL within a SQL and I need first SQL results to go one way and the second to go another way. What is happening in the SQL is that it's pulling in about 12 unions within different finance activity. All of that is loaded into a Temp table so that another SQL runs at at the end that summarizes the data only on users who edited finance data. I am thinking there isn't a way to stream out two different results so I am now looking at a way to rebuild the summary data from scratch in Alteryx.

apathetichell · Answer

Yeah - sorry - I'd have to look at the underlying SQL, but if you are unioning your results from two temp tables-  you can use a PreSQL - if you are expecting to see two different results - it won't happen. Alteryx will follow your SQL statement for your result. If you do:

presql:

create temp table 1;

create temp table 2;

union/select data from  temp table 1& 2 to create temp table 3;

drop temp table 1;

drop temp table 2;

sql

select * from temp table 3;

sure - but you cannot extract temp table 1 and temp table 2 without specifying a manner to connect them.