This is an extraction from Thread id 715045, Message ID 715885. Maybe this could help.



SELECT a.id, a.nameinfo...
FROM names a
WHERE TTOC(a.effective_date, 1) =
(SELECT MAX(TTOC(b.effective_date, 1)) FROM names b WHERE b.id = a.id)
UNION
SELECT 0 AS id, nameinfo AS ...
FROM names a
WHERE RECNO() = 1
INTO viewNames
ORDER BY 1


SELECT a.*, b.nameinfo..., c.addressinfo
FROM ids a LEFT JOIN viewNames b
ON a.id = NVL(b.id, 0)
LEFT JOIN viewAddress c
ON a.id = NVL(c.id, 0)