Re: HELP: I am desperate(view problems)

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/12/01
Message-ID: <34837BED.2686@iol.ie>#1/1

Casper Thrane wrote:
>
> Hi!
>
> I have the following problem.
>
> I have three tables which looks like this:
>
> account:
> actid
> ..
> ..
> ..
> / \
> / \
> / \
> usage: budget:
> actid actid
> transdate transdate
> amount amount
>
> In this model there is a budget transaction the 1st in every month.
>
> Now I want to create a view which should look like this:
>
> view:
> actid transdate usageamount budgetamount
>
> example
> account:
> actid 1014
>
> usage:
> actid 1014
> transdate 19970303
> amount 800
>
> actid 1014
> transdate 19970305
> amount 800
>
> budget:
> actid 1014
> transdate 19970103
> amount 1900
>
> the view
> actid transdate usage budget
> 1014 19970101 0 1900
> 1014 19970303 800 0
> 1014 19970305 800 0
>
> If there isn't any usage or a any budget for a specific date, there has
> to be a zero.
>
> My problem is, what ever I do, I end up with the Cartesian product.
>
> HEEEELLLLPPPPPPP!!!!!!!
> --
> Casper Thrane
> System developer
> Benau A/S

Casper,

   You appear to need a two-sided outer join between USAGE and BUDGET on the join key (actid,transdate), since any given value of this key may be missing from either table.

  Althought the two-sided outer-join is not supported in Oracle (since it is not a standard relational construct), there are traditionally two ways of achieving this:   

1. Outer-join both tables to a third table containing all valid values of (actid,transdate) thus:

select ...
from usage U, budget B, valid_keys K
where (U.actid(+) = K.actid and U.transdate(+) = K.transdate)  and (B.actid(+) = K.actid and B.transdate(+) = K.transdate)  and ...  

2) Make a UNION of two single-sided outer-joins:

select ... from usage U, budget B
where U.actid(+) = B.actid and U.transdate(+) = B.transdate
 and ...

UNION
select ...
from usage U, budget B
where B.actid(+) = U.actid and B.transdate(+) = U.transdate  and ...  

A third alternative combines these two by defining VALID_KEYS (above) as a union of USAGE and BUDGET:

select ...
from usage U

    ,budget B
    ,(select actid, transdate from usage U1

      UNION
      select actid, transdate from budget B1
     ) K

where (U.actid(+) = K.actid and U.transdate(+) = K.transdate)  and (B.actid(+) = K.actid and B.transdate(+) = K.transdate)  and ...

Finally, if you want to include any columns from ACCOUNT itself this must be joined to each non-outer table, i.e. those WITHOUT the (+).

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"

Received on Mon Dec 01 1997 - 00:00:00 CST