asked on
WITH emp AS (
SELECT * FROM emp_owner.emp_employee
WHERE ( statusid IN (1,2,3,7,8,9,14) OR ( statusid IN (30,31) AND terminationdate + interval '1' day > sysdate ) ) AND LastName IS NOT NULL AND LocationID NOT IN (0,99)
ORDER BY LastName, FirstName ),
h AS (
SELECT EmployeeID, StatusID FROM emp_employeehistory
WHERE StatusID NOT IN (30, 31)
ORDER BY EmployeeID, EmployeeHistoryID DESC)
SELECT EmployeeId,
(SELECT StatusID
FROM h WHERE RowNum = 1) AS CurrentStatus,
FirstName, LastName
FROM emp;
Employee Table
EmployeeID LastName FirstName
1 Doe John
2 Smith Jane
3 Jones Tom
Employee History Table
EmployeeHistoryID EmployeeID StatusID
1 1 1
2 2 1
3 1 2
4 3 1
5 1 30
6 2 31
7 3 1
EmployeeID StatusID
1 2
3 1
2 1
ASKER
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
TRUSTED BY
locationid, terminationdate, statusid
I faked it with the following values for each emp_employee record
locationid = -1
terminationdate = sysdate+7
statusid = 1
and then correlating the subquery by employeeid, this returned the data you were looking for
Open in new window