Tricky EFFDT Logic in Query

Sometimes you want to write something like the following in Query
(code snippet for outer join + effdt solution btw):

AND K.PASSWORD (+) = F.PASSWORD
AND

(K.EFFDT IS NULL
OR K.EFFDT =
(SELECT MAX( M.EFFDT)
FROM PS_UQ_CHK_SIG_FILE M
WHERE M.EFFDT <= SYSDATE))


It would appear that Query doesn't let you, but if you do things in the correct order you can force it to do something it was specifically designed to not do. : )

  1. Add your subquery
  2. Allow the default Effdt Logic to be added by PeopleSoft/Query
  3. Add a new expression "max(effdt)" and add as a selected field.
    Note: do not add the alias, just effdt.
  4. Correct the Criteria.
Some subqueries don't use effdt logic, the important step is to not add the alias in the expression, until you've finished all the criteria.

0 comments: