A SQL solution possible?

From: Ram K <lambu999_at_gmail.com>
Date: Tue, 5 Apr 2005 03:35:47 -0700
Message-ID: <db3c8dbf05040503355e12cd2f@mail.gmail.com>

Hi all,   

ver: Oracle 9i   

I have the following 3 tables:

SQL> select * from account_tbl; --> can be thought of as designation master table

DESIGNATION

---

       1043
       1044
       1045
       1046
       1047

04:37:34 SQL> select * from fiscal_year;  --> year master table
      YEAR


----------

      1999
      2000
      2001
      2002
      2003
      2004
      2005

There is a table called destination which has rows like this:

05:19:27 SQL> select * from destination; more...

DESIGNATION FISCAL_YEAR AVG_AMT TOT_AMT COMMENTS
----------- ----------- ---------- ---------- ----------

       1043        2000        100        100 good one
       1044        2000        300        400 not good
       1046        2000        500        500
       1043        2002        100        100
       1044        2002        100        100 low
       1045        2002        100        100
       1046        2002        100        100 so so..
       1047        2002        300        300 Ok
       1046        2003        500        600
       1044        2004        120        200
       1045        2004        130        200
       1047        2005        200        400 moderate

12 rows selected.

---

What I WANT for output is :

DESIGNATION  YEAR     AVG_AMT  TOT_AMT 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



-----------------------------------------------------------------------------------------

In other words there has to be one row output for every designation (in the account_tbl) for every year (in the fiscal_year table).

This is not going to be run on an OLTP application and so the time is not a major issue. It probably is going to run against several 10s of thousands of rows. (not sure on the number of rows at this point)   

I am thinking we can write a Pipelined function for this. Is there anyway other than that, possibly get it done through SQL only, using views also if needed?    

Thank you all,
Ram.
--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 05 2005 - 06:39:37 CDT