Hello Sergey,

Thought I would give you the final version of the solution. Thanks again for all the help.

This makes it look so simple. Because of the multiple pass, I added all of my "names" and "address" logic to the viewNames and viewAddress queries. Once this was done, I could make my main query much simpler. Note the "ON a.id = NVL(b.id, 0). This works like a default record value; which is blank.

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)


Of course my queries look a lot different than the above, but the concept works.

Thanks again.