Re: Re: A SQL solution possible?

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
>
>--
>http://www.freelists.org/webpage/oracle-l

• = = = = = = = = = = = = = = = = = = =

jametong
jametong_at_gmail.com
2005-04-05

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