4

For examples, lets say I need to do set operations and joins on below result sets from 3 different SQL queries:

  • I don't want to write a composite query with subqueries, or use temporary database tables, views or CTEs. In other words, no processing on the database server but on the client machine.
  • I am not asking about GUI based query builders like SQLyog

Something which would enable faster data analysis using broken-down sets of data like below from a large database. It could be roughly like if you could join two result sets from two separate queries in Toad/SQL developer. (What I find myself doing now to achieve this is pasting multiple sets of data from each of these queries into MS excel and use vlookup() and further filtering etc.)

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 operation to be done on T1,T2,T3 from above 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 generates SQL like above to process on top of the result sets, SQL above is only for conveying the logic I have in mind.)

Required result is:

Col1  Col4
1    #

[EDIT]

This was previously asked here on DBA.SE and has been migrated by the OP.

4
  • 2
    Why not using views + some visual query builder‎? – Franck Dernoncourt Aug 6 '14 at 20:08
  • I had given simple examples for illustration only, actual queries would be ad-hoc and complex (results in few 100k rows). Focus of the question is operating on result sets locally after fetching from the database. – dbza Aug 6 '14 at 20:44
  • 2
    See my comment about XE on your DBA.SE question - any use to you? Create a link and do your stuff locally on it. See P. Forstmann's answer here (on Oracle forums). BTW, I use the name Vérace on DBA.SE. – Vérace Aug 6 '14 at 20:59
  • Many reporting tools let you do this (I remember this from the time I was working in Business Objects, now bought by SAP). They let you define an intermediate layer which contains what you describe. Not exactly ad-hoc, but if you have the designer module fast enough to put together - with the added ability to store the intermediate design. – user416 Apr 5 '15 at 3:45
1

Using pandas data frames you can run an initial query, (or set of queries), to populate the data frames and then perform all of your joins, manipulation, reporting, exporting, etc. offline on the client machine.

Better yet you can create a script that will, on demand, perform all of the above actions so as to rerun your process with fresh data as needed.

It will probably be worth reading the comparison between Pandas and SQL page.

1
  • Awesome! Looks like this would help me. Thanks so much! – dbza Aug 3 '15 at 18:03
0

I will continue all correspondance about this issue here. As I suggested, you can still use XE with Oracle's heterogeneous services, you can connect to DB2 and MS SQL Server.

[EDIT]

You can connect SQL Developer using JDBC. Check here and here (first two results Googling "JDBC and Oracle SQL Developer), and esp. here (third result). Have "messed about" with MySQL using SQL Developer - give it a go.

4
  • From the first link to DB2 I gave "CREATE DATABASE LINK eetest_link CONNECT TO "user" IDENTIFIED BY "password" USING 'eetest';" - so I think that one can assume that yes, it works something like that - try it and get back to us if there are problems. You're not the first person ever to want to connect to different databases. – Vérace Aug 6 '14 at 21:30
  • Maybe something like "CREATE (Oracle) TABLE AS SELECT My_Stuff FROM DB2_Link...? Can't remember off the top of my head - don't have running system here! – Vérace Aug 6 '14 at 21:34
  • That could work if XE can link to DB2 like you suggested, but this is not really what I am looking for- as i had mentioned inthe question no temporary tables or views. Thanks! – dbza Aug 6 '14 at 21:36
  • Select your data into a "real" (i.e. permanent on disk) Oracle table - not necessarily a temporary one. – Vérace Aug 6 '14 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.