Re: SQL Approach, Multiple Tables, UNION?

Billy Verreynne wrote:
>
> As I read it Tim, your biggest problem is that the table to which a FK
> refers to, is also a column value in the current row.
>
> This sounds like something that a pipeline table function can nicely
> deal with.

<snip>

Thank you Billy. This approach sounds like it's making use of the object features that were released with 8i? I'm not all that familiar with it and will have to bone up on it a bit. The book I was using (Palinski) to get up to speed on the inline views Daniel suggested does have a section on these object features.

A quick skim of the section, though suggests to me that I may have to rejig the database structure a bit to come up with the TWorkOrderRow object you suggested? The TWOrderReport approach you mentioned almost sounds like denormalizing the structure a bit for reporting purposes, anyway. I've done this before for reporting purposes in other applications (mostly Jet). In fact, the app I'm working on now is actually meant to replace a reporting application I wrote some years ago, which uses Jet SQL to copy data from linked Oracle tables and consolidate them into a smaller number of tables which makes recordset return a whole lot faster than if I used proper joins on all the tables...

> One thing that I will not consider doing unless there's no other way -
> using MS Access to build an Oracle query.

Definitely not. While I used to do this and still think the Jet linked table features in an Access mdb are great in that they are quick and dirty from a database admin's point of view with respect to trouble shooting, I've been avoiding Jet SQL for the past year or so and relying completely on passing actual Oracle SQL from Access to the oracle server via the Access "pass through query" feature. It's generally faster and gives better results, plus utilizes the server rather than the local machine which a straight ODBC linked table does (see my last response to Daniel).

About the only thing I've come acrosss so far that I can't do in Oracle is cross tables! My kingdom for an Oracle crosstab query!

Thanks again, please let me know if I'm totally out to lunch on the above interpretatio of Oracle Object stuff. Otherwise, I think I can manage to figure it out from Palinksi's book...

-- 
Tim - http://www.ucs.mun.ca/~tmarshal/
 ^o<  
 /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^  "Want some?" - Ditto