SQL Query that generates two distinct result tables

[JPSeagull]

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]

You are running two sql statements. You will either union them using a union statement in your sql - or you will need two connect in-dbs/two input data tools. You cannot create two distinct in-memory datasets from one SQL query.

[JPSeagull]

@apathetichell You can't run SQL with In-DBs that has CTEs or Temp tables. I would love to know how to work around that.

I didn't say I created two distinct in memory sets in the SQL. I have one SQL where the top of it creates temp tables and CTEs. It runs a query extracting all the data needed then it spits out a display of that results while part 2 of the SQL is using the same Temp Tables to generate the second results. At the end... we DROP the temp tables.

[apathetichell]

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.

[JPSeagull]

@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]

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.