To get the Execution plans to match between the old and new syntax, the "old style" join query needs to look something like this:

SELECT

S.IIATransactionId

,substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionId

FROM iiafeedtransaction S,

ratsiiafeedtransaction o

WHERE S.IIATransactionId *= substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)

GROUP BY

S.IIATransactionId,substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)

HAVING substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) IS NULL

ORDER BY S.IIATransactionId

Using the GROUP BY and the HAVING clauses puts the filter back into the execution plan and the correct results are seen.

Far easier to just use the new style of joins

Regards,

Mark Horton.