Re: SQL Approach, Multiple Tables, UNION?

Tim Marshall <tmarshal_at_Gunner.Sabot.Spam.On.Loaded.FIRE> wrote i

> This approach sounds like it's making use of the
> object features that were released with 8i?

That's correct.

> 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?

Not really. You simply decide on the transformation structure - i.e. how the transformed rows should look like. Kind of like creating a reporting table. You decide on the row structure. Which columns it will contain. What derived and calculated columns. Which look-up columns.

Then you write a function that accepts the report input data and transforms it into one or more reporting rows.

> The TWOrderReport approach you mentioned almost
> sounds like denormalizing the structure a bit for reporting purposes,
> anyway.

Yes. Kind of. I'm not sure how you want your reporting row to look like. The basic idea behind pipeline table functions is transformation of data.

You send a row to the function. The function changes that into a completely different row and returns any number of those row per input row.

You decide the structure of the output row.

The great things about this is that the function looks like a table from SQL. Even better, it is a table that accepts rows as input.

> 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.

Good show of common sense. Jet SQL is a real interference when dealing with foreign databases.

> 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!

That's pretty easy. :-)

How do you want it? Vanilla SQL flavour (which can be done on any SQL database)? Oracle DECODE flavour? Oracle CASE flavour? Or Oracle pipeline table flavour? :-)

> 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...

The object features as far as pipeline tables go, is pretty simple once you understand the basic concept. And for that you simply need the Oracle Manuals.

Oracle9i Application Developer's Guide - Fundamentals Section 9 : Using Procedures and Packages (Returning Large Amounts of Data from a Function) http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#34912

BTW, the source sample given there is a pivot/crosstab function.

--
Billy