I think in that case the problem is not the old or the new syntax I think it was an error in how the query was written.

Just write the following and it will give yoy the same result:

(note that I don't use the outer join symbol, it is not necessary here!).

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)

AND  o.Rats_filename IS NOT NULL

ORDER BY S.IIATransactionId