Thanks Sergey, your help led me to a path that would work. What was required to work around the problem was a dumby record.
SELECT STR(a.id, 19) + MAX(TTOC(a.effective_date, 1)) max_dt ;
FROM names a ;
GROUP BY a.id ;
UNION ;
SELECT DISTINCT STR(0, 19) + TTOC(DATETIME(2000,01,01,12,00,00), 1) max_dt ;
FROM names a ;
WHERE 1 = 1 ;
INTO CURSOR viewNames_Max ;
ORDER BY 1
The first query pulls all of the valid records, the second creates the dumby record. Do you know of a way to create the record without using "WHERE 1 = 1" and "DISTINCT"?
Your multiple pass now comes into play with one of my prior tests.
SELECT a.*, b.nameinfo...;
FROM ids a LEFT JOIN names b ;
ON a.id = b.id ;
WHERE STR(NVL(b.id, 0), 19) + TTOC(NVL(b.effective_date, DATETIME(2000,01,01,12,00,00)), 1) IN (SELECT max_dt FROM viewNames_Max) ;
GROUP BY a.id
Because the Dumby record has been added and we use NVL() to default for NULL values, we will get a hit every time. We also know that an ID of zero is not valid combined with the forced date would force it to be unique. Now it should work for multiple tables as well!