RE: Re: A SQL solution possible?

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
 

--
http://www.freelists.org/webpage/oracle-l