Ok, here goes.... Some people felt and probably correctly that I did not fully explain what when wrong with the 1st 2 queries in the discussion.

Query1:

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

Did not return the expected results because there o.rats_filename column does not contain nulls.  The nulls are returned as a result of the outer join.  So it correctly returned information based solely of the data in the tables being evaluated.

Query 2:

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 NULL

ORDER BY S.IIATransactionId

Returned with all the second column values being set to NULL because again there are no NULL's in the o.rats_filename column.  This meant that there where no matches to the Outer Join since it was limited to only the NULL values in the table.

Query 3:

SELECT S.IIATransactionId

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

FROM iiafeedtransaction S

LEFT OUTER JOIN ratsiiafeedtransaction o

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

WHERE o.Rats_filename IS  NULL

ORDER BY S.IIATransactionId 

Although, it did supply the desired results was not as efficient as the query I posted.  I was only trying in my posting to supply another example of how it could be done.

Some of the postings seemed to be getting lost in the "substring(rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36)" and the examples posted where overly complicated which would burn up processing time.  I realize that in an age where processors are very quick and memory seems hard to exhaust.  But, If a Query is overly complicated and you are trying to process Thousands of records... Every little piece of time can add up.

I hope this helps...