Re: Re: A SQL solution possible?
- From: "jametong@xxxxxxxxx" <jametong@xxxxxxxxx>
- To: "breitliw@xxxxxxxxxxxxx" <breitliw@xxxxxxxxxxxxx>, "lambu999@xxxxxxxxx" <lambu999@xxxxxxxxx>
- Date: Tue, 5 Apr 2005 20:09:34 +0800
Hello Wolfgang Breitling,
This sql can get the answer.
SQL> select a.designation,a.year,nvl(b.avg_amt,0) avg_amt,
2 nvl(b.tot_amt,0), b.comments
3 from (
4 select designation,year
5 from account_tbl,fiscal_year
6 ) a,destination b
7 where a.designation = b.designation(+)
8 and a.year = b.fiscal_year(+)
9 /
DESIGNATION YEAR AVG_AMT NVL(B.TOT_AMT,0) COMMENTS
----------- ---------- ---------- ---------------- --------------------
1043 1999 0 0
1043 2000 100 100 good one
1043 2001 0 0
1043 2002 100 100
1043 2003 0 0
1043 2004 0 0
1043 2005 0 0
1044 1999 0 0
1044 2000 300 400 not good
1044 2001 0 0
1044 2002 100 100 low
DESIGNATION YEAR AVG_AMT NVL(B.TOT_AMT,0) COMMENTS
----------- ---------- ---------- ---------------- --------------------
1044 2003 0 0
1044 2004 120 200
1044 2005 0 0
1045 1999 0 0
1045 2000 0 0
1045 2001 0 0
1045 2002 100 100
1045 2003 0 0
1045 2004 130 200
1045 2005 0 0
1046 1999 0 0
DESIGNATION YEAR AVG_AMT NVL(B.TOT_AMT,0) COMMENTS
----------- ---------- ---------- ---------------- --------------------
1046 2000 500 500
1046 2001 0 0
1046 2002 100 100 so so..
1046 2003 500 600
1046 2004 0 0
1046 2005 0 0
1047 1999 0 0
1047 2000 0 0
1047 2001 0 0
1047 2002 300 300 Ok
1047 2003 0 0
DESIGNATION YEAR AVG_AMT NVL(B.TOT_AMT,0) COMMENTS
----------- ---------- ---------- ---------------- --------------------
1047 2004 0 0
1047 2005 200 400 moderate
35 rows selected.
SQL>
Best regards,
======= At 2005-04-05, 19:52:05 you wrote: =======
>Try this
>
>select b.DESIGNATION, b.YEAR, nvl(a.AVG_AMT,0), nvl(a.TOT_AMT,0), a.COMMENTS
>from destination a,
> (select designation, year from account_tbl, fiscal_year) b
>where a.designation(+) = b.designation
>and a.fiscal_year(+) = b.year
>
>
>
>
>Regards
>
>Wolfgang Breitling
>Centrex Consulting Corporation
>www.centrexcc.com
>
>--
>//www.freelists.org/webpage/oracle-l
= = = = = = = = = = = = = = = = = = = =
jametong
jametong@xxxxxxxxx
2005-04-05
--
//www.freelists.org/webpage/oracle-l
Other related posts:
