Which tool enables using SQL query result sets as temporary tables for further analysis and set operations? [closed]

Question

For examples, lets say I need to do set operations and joins on below 3 result sets.

I don't want to write a composite query or use temporary database tables. Something which would enable faster data analysis using broken-down sets of data like below from a large database. Like as if you could join two result sets in toad or SQL developer with the help of a GUI.

SQL Query 1 result: (T1)

Col1 Col2 
A    1
B    2

Query 2 result: (T2)

Col3 Col4 
1    #
2    *

Query 3 result: (T3)

Col5   
1     
3    

---

Required output is

 Select T1.col1,T2.col4 
        from T1,T2 
        where T1.col2=T2.col3 
        and T1.col2 in (Select T3.col5 from T3)

(Not necessary that the tool should generate SQL like above to process the result sets, SQL is only for conveying the logic.)

Required result:

Col1  Col4
1    #

Answer 1

It seems to me that you might want one of two things to do what you require.

1) could be global temporary tables (GTTs), see here or here. Check out the Oracle docco here for drier technical detail.

2) might be CTEs - Common Table Expressions (or subquery factoring as Oracle calls it) - also known as the "WITH CLAUSE". Howtos are here and here - (again, rather dry) Oracle docco is here.

One answer to your question is that every tool which supports Oracle supports "using SQL query result sets as temporary tables for further analysis and set operations" - this capacity is native to Oracle and available to all.

As for doing this with a GUI and dragging and dropping lines between either GTTs or CTEs - I'm not sure such a tool exists - but you could experiment with creating your GTT/CTE as a real concrete table and then do the Drag'n'Drop bit and when you're finally getting the result you want, you could just use the GUI tool generated SQL. Better though to learn SQL and be able to do this without a GUI.

Answer 2

I agree that a temporary table (or "hash table" # in SQL Server speak) will do what you want.

If you want to do this graphically I can recommend a product called "Lavastorm Analytics BRE". There is a free desktop version and it will allow you to view datasets on the fly. It can also do a few things you can't do with conventional SQL such as an inner and outer join in a single query.