Problem Statement:
I am performing an outer join between 2 tables. On the rows where are no matching values, NULLs are in the column. All this is expected.
Now what I would like to happen is that instead of a NULL, the previous value in the column is used.
Example
Table 1 - CHANGEDATA
Input_month | Team_Name | ChangeNumber
--------------------------
----------
----
1/1/2008 CC 12
2/1/2008 CC 3
3/1/2008 CC 0
4/1/2008 CC 0
5/1/2008 CC 4
6/1/2008 CC 3
7/1/2008 CC 0
8/1/2008 CC 2
9/1/2008 CC 4
10/1/2008 CC 10
11/1/2008 CC 0
12/1/2008 CC 0
Table 2 - BASEDATA
Input_month | Team_Name | Baseline
--------------------------
----------
----
1/1/2008 CC 100
4/1/2008 CC 111
6/1/2008 CC 120
10/1/2008 CC 125
Doing an outer join yields the following
Input_month | Team_Name | ChangeNumber | Baseline
--------------------------
----------
----------
----
1/1/2008 CC 12 100
2/1/2008 CC 3
3/1/2008 CC 0
4/1/2008 CC 0 111
5/1/2008 CC 4
6/1/2008 CC 3 120
7/1/2008 CC 0
8/1/2008 CC 2
9/1/2008 CC 4
10/1/2008 CC 10 125
11/1/2008 CC 0
12/1/2008 CC 0
Now here is what I would like:
Input_month | Team_Name | ChangeNumber | Baseline
--------------------------
----------
----------
----
1/1/2008 CC 12 100
2/1/2008 CC 3 100
3/1/2008 CC 0 100
4/1/2008 CC 0 111
5/1/2008 CC 4 111
6/1/2008 CC 3 120
7/1/2008 CC 0 120
8/1/2008 CC 2 120
9/1/2008 CC 4 120
10/1/2008 CC 10 125
11/1/2008 CC 0 125
12/1/2008 CC 0 125
Where if Baseline is NULL, I would like the previous non null value.
NON WORKING SOLUTION.
My original solution was to use a User Defined Function to return the Baseline number
So the query would look like this
select a.input_month, a.Team_name, a.ChangeNumber,
NVL(b.baseline, LastValue(a.input_month, a.Team_Name)) "Baseline"
from CHANGEDATA a, BASEDATA b
where a.input_month = b.input_month (+)
and a.team_name = b.team_name (+)
This query only yielded one row, the first one.
The problem is that the User Defined Function, LastValue querys the table. If LastValue just returns a hardcoded value, I get all the rows back, but the Baseline value does not show the last value.
The following is the LastValue Function:
create or replace function lastvalue(nmonth date,nteam varchar2) return number
as
newbaseline number;
begin
select baseline into newbaseline
from
(select baseline, max(input_month)
from
BASEDATA
where input_month <= nmonth
and TeamName = nteam
group by baseline);
return newbaseline;
end lastvalue;
/
If you run this function from a single row select, it does return the proper value, but when you expect multiple rows, I only get one.
Question:
Is there a different SQL syntax that will give me what I want?
OR
What am I doing wrong in LastValue definition that is causing it to return only one row?
Example Data Creation Statement:
CREATE TABLE CHANGEDATA
(INPUT_MONTH DATE,
TEAM_NAME VARCHAR2(10),
CHANGENUMBER NUMBER)
/
INSERT INTO CHANGEDATE VALUES (TO_DATE('JAN-01-2008','MO
N-DD-YYYY'
),'CC',12)
;
INSERT INTO CHANGEDATE VALUES (TO_DATE('FEB-01-2008','MO
N-DD-YYYY'
),'CC',3);
INSERT INTO CHANGEDATE VALUES (TO_DATE('MAR-01-2008','MO
N-DD-YYYY'
),'CC',0);
INSERT INTO CHANGEDATE VALUES (TO_DATE('APR-01-2008','MO
N-DD-YYYY'
),'CC',0);
INSERT INTO CHANGEDATE VALUES (TO_DATE('MAY-01-2008','MO
N-DD-YYYY'
),'CC',4);
INSERT INTO CHANGEDATE VALUES (TO_DATE('JUN-01-2008','MO
N-DD-YYYY'
),'CC',3);
INSERT INTO CHANGEDATE VALUES (TO_DATE('JUL-01-2008','MO
N-DD-YYYY'
),'CC',0);
INSERT INTO CHANGEDATE VALUES (TO_DATE('AUG-01-2008','MO
N-DD-YYYY'
),'CC',2);
INSERT INTO CHANGEDATE VALUES (TO_DATE('SEP-01-2008','MO
N-DD-YYYY'
),'CC',4);
INSERT INTO CHANGEDATE VALUES (TO_DATE('OCT-01-2008','MO
N-DD-YYYY'
),'CC',10)
;
INSERT INTO CHANGEDATE VALUES (TO_DATE('NOV-01-2008','MO
N-DD-YYYY'
),'CC',0);
INSERT INTO CHANGEDATE VALUES (TO_DATE('DEC-01-2008','MO
N-DD-YYYY'
),'CC',0);
CREATE TABLE BASEDATA
(INPUT_MONTH DATE,
TEAM_NAME VARCHAR2(10),
BASELINE NUMBER)
/
INSERT INTO BASEDATA VALUES (TO_DATE('JAN-01-2008','MO
N-DD-YYYY'
),'CC',100
);
INSERT INTO BASEDATA VALUES (TO_DATE('APR-01-2008','MO
N-DD-YYYY'
),'CC',111
);
INSERT INTO BASEDATA VALUES (TO_DATE('JUN-01-2008','MO
N-DD-YYYY'
),'CC',120
);
INSERT INTO BASEDATA VALUES (TO_DATE('OCT-01-2008','MO
N-DD-YYYY'
),'CC',125
;
NORMAL OUTER JOIN
select a.input_month, a.Team_name, a.ChangeNumber,b.baseline
from CHANGEDATA a, BASEDATA b
where a.input_month = b.input_month (+)
and a.team_name = b.team_name (+)
Start Free Trial
