Re: HELP: I am desperate(view problems)
Date: 1997/12/02
Message-ID: <661bbm$n9e$1_at_news.usf.edu>#1/1
Basically the problem is that you can outerjoin a table at most to one other table. So what you do to get around that is first select all the records that are in the BUDGET table and not in the USAGE table. Next select all the records that are in the USAGE and not in the BUDGET table. Next select all the records which are in both USAGE and BUDGET. Lastly UNION the results together to the full result set. NOTE: Watch the performance or lack there of of the following SELECT. It will need to be tuned.
SELECT ac.actid, bd.transdate, bd.amount budget, 0 usage FROM account ac, budget bd, usage ug
WHERE ac.actid = bd.actid
AND bd.actid = ug.actid (+)
AND bd.transdate = ug.transdate (+)
AND ug.actid IS NULL
UNION
SELECT ac.actid, ug.transdate, 0 budget, ug.amount usage FROM account ac, budget bd, usage ug
WHERE ac.actid = ug.actid
AND ug.actid = bd.actid (+)
AND ug.transdate = bd.transdate (+)
AND bd.actid IS NULL
UNION
SELECT ac.actid, bd.transdate, bd.amount budget, ug.amount usage FROM account ac, budget bd, usage ug
WHERE ac.actid = ug.actid
AND bd.actid = ug.actid
AND bd.transdate = ug.transdate
/
Test run from your date:
ACTID TRANSDATE BUDGET USAGE
- --------- ---------- ---------- 1014 03-JAN-97 1900 0 1014 03-MAR-97 0 800 1014 05-MAR-97 0 800
Hi!
[Quoted] I have the following problem.
[Quoted] 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 0Received on Tue Dec 02 1997 - 00:00:00 CET