Re: SQL Approach, Multiple Tables, UNION?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 8 Sep 2003 03:05:32 -0700
Message-ID: <1a75df45.0309080205.724e75f5@posting.google.com>

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

> IS there a better approach to the following other than using the UNION
> operator several times? Of course, I would like to avoid functions such
> as decode or Case in the where clause.... The work environment is a
> facilities maintenance/management organization.
<snipped>

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.

You define UDT (User Defined Type) that represents the row type that the MS Access reporting "thing" will see. E.g. create or replace type TWorkOrderRow as object ( workorder_pk

    work order other reporting columns
    work order equipment reporting columns     ...
    work order vehicle reporting columns )

Then the reporting table UDT, e.g.
create or replace type TWorkOrderReport as table of TWorkOrderRow

Then you define a pipeline function. The input into this function is a cursor that selects the applicable work orders to report on, e.g. CURSOR( select f.* from F_WORKORDER f where bluh )

The output from this function is a "virtual/dynamic table" of type TWOrderReport, containing TWorkOrderRow rows.

In the function, you loop through the input cursor, access the various other tables as required, and throw one or more (or none) TWorkOrderRows at Oracle to return as part of the result set.

From the MS Access side, the query will look something like this:

select
  *
from TABLE( pipelinefunction_name(

              CURSOR( select f.* from F_WORKORDER  f where :criteria )
            )
     )

where :optionally_some_more_criteria

The only real concern IMO is performance. If the PL/SQL logic in that pipeline function loop is complex and it is looping through several tons of data, the processing overheads per input row can add up to a substancial elapsed time.

In that case, you need to make sure that the code within the loop is as tight and optimised as possible. You can also consider using parallel query, running several processes in parallel, each giving rows to the pipeline table and receiving results back.

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

If the pipeline function thing does not work (which I don't really see why not), I will rather have the logic in Oracle in some other way (e.g. PL/SQL using dynamic SQL and returning a reference cursor or UNIONs with VIEWs), instead of putting that into MS Access where only that MS Access application has access to the reporting output from data in Oracle.

The report layer should only deal with the formatting and representation of information.

--
Billy

Received on Mon Sep 08 2003 - 05:05:32 CDT