Re: HELP: I am desperate(view problems)

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 approach you might want to consider:

select

   a.actid actid,
   a.transdate transdate,
   nvl(b.amount, 0) usage,
   nvl(c.amount, 0) budget
from
  (select actid, transdate from po7.usage

     union
   select actid, transdate from po7.budget) a,    po7.usage b,
   po7.budget c
where

   a.actid = b.actid (+) and
   a.actid = c.actid (+) and
   a.transdate = b.transdate (+) and
   a.transdate = c.transdate (+) ;

This should give you the result you're looking for, according to your example. You won't need to look at the account table unless you need to include accounts in your result set which have no transactions (use an outer join) and/or you'd like to include some columns from the account table in your result set.

Note: if you're running an older version of Oracle (ie. prior to 7.3) you'll need to create and use a view for the first 'from' clause instead.

Regards,

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