Outer Joins and Effdt June 26, 2007Posted by Duncan in Oracle, PeopleSoft, PeopleTools, SQL.
I was asked by one of our Junior Consultants the best method of outer joining to a table containing Effdt (which you’d want to do a Max Effdt sub-query on).
I provided a solution, but after pondering the issue further I realised there are a number of ways to do this.
For those not familiar with the problem, an Outer Join is a specific SQL statement where all rows from the first table are returned even if no corresponding row in the second table is found. Values from the second table are shown where they exist, defaulting to null when they do not. The issue arises as the second table has an effdt column, which usually necessitates a sub query (to find the row with the greatest effdt less than a supplied as of date). This would be a sub-query, and you can’t outer-join to a subquery.
So, which is the best solution?
NVL both sides of the Subquery
The answer I gave was to apply a NVL to both sides of the sub-query thus:
and NVL(a.effdt,'01/JAN/1901') = (select NVL(max(a1.effdt),'01/JAN/1901') from ....
This worked fine in this example, but I’m wondering if there is a more elegant solution.
Normal Effdt plus Effdt is Null
An alternative would be to have the Effdt sub-query as normal (which would return any rows having an Effdt matches the sub-query Effdt) and then adding
or Effdt is null
This is a solution I’ve seen used the most, but to my mind it would omit data which has a row in the second table – but where the Effdt is after the AsOfDate. Therefore it wouldn’t be returned.
Possibly the solution involving the most lines of code is creating an inline view. The Inline view would contain the second table and the subquery, in effect ‘flattening’ them. The first table can have an outer join to this Inline View without having to worry about the subquery.
It would look something like this:
FROM PS_PERSONAL_DATA PD
, (SELECT *
FROM PS_JOB J
WHERE J.EFFDT = (SELECT MAX(J1.EFFDT)
FROM PS_JOB J1
WHERE J1.EMPLID = J.EMPLID
AND J1.EMPL_RCD = J.EMPL_RCD
AND J1.EFFDT <= SYSDATE)) J
WHERE PD.EMPLID = J.EMPLID (+)
Does anyone have a better suggestion or comment on the above methods?