I have a situation in where I am trying to essentially get a list of all changes that have occurred on a dataset between two points in time. This seems like a very good case for the VERSIONS BETWEEN keyword in Oracle, but I do not know how to effectively join to other tables and get the results that I want.
Let's say I have the following tables:
CREATE TABLE emp (
emp_id NUMBER(5) PRIMARY KEY,
employee_name VARCHAR2(20)
dept_id NUMBER(3) NOT NULL
CONSTRAINT admin_dept_fkey REFERENCES dept
(dept_id))
);
CREATE TABLE dept(
dept_id NUMBER(5),
department VARCHAR2(20)
);
If an employee record changes, I need to be able to get the department name as it was at the time the employee record was changed, because it's possible that the employee still belongs to the same department but the department name was changed at some point.
Example: EMP.VERSIONS_STARTSCN EMP_ID EMPLOYEE_NAME DEPT_ID DEPARTMENT 1 1 Michael 1 Services 0 1 Michael 1 Sales
If the database were denormalized and I was storing the department name on the table itself, this would be incredibly easy; however, using the lookup table seems to make it less intuitive.
Things that I have tried:
SELECT *
FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE e
JOIN dept d ON d.dept_id = e.dept_id;
This will get me the department as it exists today, but that doesn't tell me what it was at the time the employee record was changed.
SELECT *
FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE e
JOIN dept VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE d
ON d.dept_id = e.dept_id;
This isn't good either, because it's going to return multiple rows, one for the employee with each department change where I actually only want the department as it existed at the time the employee record was changed.
I have also tried a couple of inner selects, but it seems like Oracle doesn't allow me to use the VERSIONS_ENDSCN or VERSIONS_STARTTIME in the inner query. Variations I have tried include:
SELECT
versions_startscn,
e.employee_name,
(SELECT department FROM dept AS OF SCN versions_startscn d WHERE d.dept_id = e.dept_id)
FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE e
SELECT
e.employee_name
(SELECT department FROM dept AS OF SCN versions_startscn d WHERE d.dept_id = e.dept_id)
FROM(
SELECT
versions_startscn,
e.employee_name,
FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE e
)
Both of these queries throw an error that VERSIONS_STARTSCN is not declared.
I feel like this has to be a fairly common use case, but I haven't been able to find any examples of using VERSIONS BETWEEN with joins. I have come up with a way to do this which involves opening a cursor with the employee changes BETWEEN SCN MINVALUE AND MAXVALUE, selecting the SCN into a variable, and selecting the changes from the emp table and DEPT AS OF SCN into a nested table type, but this seems like a lot of work to do something that seems fairly straightforward. If anyone has any ideas, it would be much appreciated.
I am currently using v11.2.0.4.
