>Well, doesn't SQL do the query from the inside out?SQL is declarative language. You specify what result you want and the query optimizer decides how to get result. IOW, you cannot make any assumption how query will be executed. That what Execution Plan is for.
>IAC, my code is returning three results for many accounts. Excluding the subquery returns one per record, as intended. Here's the code for real:
<snip>
Maybe there're multiple records matching that subquery? See if DISTINCT helps. You can also simplify WHERE condition by using pattern in the LIKE operator.
SELECT DISTINCT RMASTER.RMSACCTNUM, RMASTER.RMSSTATUS,
RMASTER.ATTRNYCODE AS RmsRecvrcd,
RPRDBAL.RMSOFFCRCD, RMASTER.RMSDATEASG,
...
FROM RMASTER
INNER JOIN RPRDBAL ON RMASTER.RMSFILENUM = RPRDBAL.RMSFILENUM
LEFT OUTER JOIN RFINANL ON RMASTER.RMSFILENUM = RFINANL.RMSFILENUM
AND RFINANL.RMSTRANDTE * 1000000 + RFINANL.RMSTRANTIM =
(SELECT MAX((m.rmsTranDte * 1000000) + m.rmsTranTim)
FROM rFinAnl m
WHERE m.rmsFileNum = rMaster.RmsFileNum
AND m.rmstranamt <> 0
AND m.rmstrancde LIKE '[345]%')
WHERE (RMASTER.ATTRNYCODE = 'P013')
ORDER BY RMASTER.RMSACCTNUM
--sb--