Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HELP: I am desperate(view problems)

Re: HELP: I am desperate(view problems)

From: Carl Paquin <pitou_at_idirect.com>
Date: 1997/12/03
Message-ID: <3484fa25.0@nemo.idirect.com>#1/1

Casper Thrane wrote in message <34829F6A.35497939_at_benau.dk>...
>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
>

Hi Casper,

Here's another way to do it...

select actid, transdate, amount usage, 0 budget from usage where (actid, transdate) in (select actid, transdate from usage minus select actid, transdate from budget)

  union all

select actid, transdate, 0 usage, amount budget from budget
where (actid, transdate) in (select actid, transdate from budget minus
select actid, transdate from usage)

  union all

select a.actid, a.transdate, a.amount usage, b.amount budget from usage a, budget b
where a.actid = b.actid and a.transdate = b.transdate;

This should give you the result you're looking for as well. It might look strange but try it. My experience with the performance of the 'minus' operator has been very good to date.

Good Luck,

Carl Paquin Received on Wed Dec 03 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US