Re: SQL Approach, Multiple Tables, UNION?

On Sun, 7 Sep 2003, Daniel Morgan wrote:

> >If you're still with me, thanks for reading and TIA for any suggestions
> >other than beating up the developers of the maintenance application with
> >which I'm working! 8)
> >
> Given the data you present I'd be thinking of using in-line views ...
> not unions. And definitely native dynamic SQL.

Thank you Daniel, I appreciate your toughing it through my post! I'm somewhat familiar with in-line views and will become even more familiar with them. I think this will be easier to construct than several Union queries...

By dynamic native SQL, I hope I'm correct in assuming you mean using Oracle SQL and constructing it via a proc I run after a user has made his/her selections. Although I'm an Access hack, when working with Oracle databases, I much prefer to use pass through queries, ie, Oracle SQL and not the Jet SQL for several reasons. First, I find the theta joins in Oracle far, far easier to construct in code as opposed to the ansi style joins Access/Jet uses (the join is actually indicated in the from clause as opposed to the where clause as in Oracle). And secondly, from a performance point of view, using Jet linked tables means one is not taking advantage of the client server capabilities of Oracle: the SQL is processed in the Oracle server and then the ODBC link also means processing is performed by Jet on the client side. Which I think is kind of dumb if you can bypass the client and have the server return you your recordsets. Besides, depending on the nature of the Oracle database (often I am just writing apps for reporting on exisiting structures over which I have no control), Jet linked tables tend to crap out and drop returned data when Jet constructed queries contain more than 4 or 5 linked tables....

Thanks again for the suggestion of in-line views.

-- 
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto