A SQL solution possible?
- From: Ram K <lambu999@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 5 Apr 2005 03:35:47 -0700
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
