RE: Re: A SQL solution possible?
- From: "Ron Perrie" <ron.perrie@xxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 5 Apr 2005 14:48:33 +0100
Hi Ram,
You can use the following (which is another member's answer) and add the
"order by" at the end.
Eg:
select a.designation,a.year,nvl(b.avg_amt,0) avg_amt,
nvl(b.tot_amt,0), b.comments
from (
select designation,year
from account_tbl,fiscal_year
) a,destination b
where a.designation = b.designation(+)
and a.year = b.fiscal_year(+)
order by year,designation,avg_amt
/
Whish produces:
DESIGNATION YEAR AVG_AMT NVL(B.TOT_AMT,0) COMMENTS
----------- ---------- ---------- ---------------- ----------
1043 1999 0 0
1044 1999 0 0
1045 1999 0 0
1046 1999 0 0
1047 1999 0 0
1043 2000 100 100 good one
1044 2000 300 400 not good
1045 2000 0 0
1046 2000 500 500
1047 2000 0 0
1043 2001 0 0
1044 2001 0 0
1045 2001 0 0
1046 2001 0 0
1047 2001 0 0
1043 2002 100 100
1044 2002 100 100 low
1045 2002 100 100
1046 2002 100 100 so so..
1047 2002 300 300 Ok
1043 2003 0 0
1044 2003 0 0
1045 2003 0 0
1046 2003 500 600
1047 2003 0 0
1043 2004 0 0
1044 2004 120 200
1045 2004 130 200
1046 2004 0 0
1047 2004 0 0
1043 2005 0 0
1044 2005 0 0
1045 2005 0 0
1046 2005 0 0
1047 2005 200 400 moderate
35 rows selected.
Cheers for now,
Ron
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005
--
//www.freelists.org/webpage/oracle-l
Other related posts:
