Wrong convert from Oracle outer join to ansi outer join

Toad for Oracle
1

This

SELECT *
FROM qsn_app.tx_blurb_new bn,
qsn_app.tx_image i
WHERE i.blurb2tx_blurb_new(+) = bn.blurb_key
AND i.active_flag(+) = ‘Y’;

converts to this

SELECT *
FROM QSN_APP.TX_IMAGE I
RIGHT OUTER JOIN QSN_APP.TX_BLURB_NEW BN ON (I.BLURB2TX_BLURB_NEW = BN.BLURB_KEY)
WHERE (I.ACTIVE_FLAG /(+)/
) = ‘Y’;

which is wrong! active_flag needs to go into the “ON” criteria.

In the original SQL, if i.active_flag was compared to another column, rather that a literal, Toad does the translation correctly.

for example:

AND i.active_flag(+) = bn.active_flag

2

Yes, we have a similar case and as of 12.12 it’s still broken and produces a wrong query. As in your example, it even leaves the /* (+) */ in a comment, hinting to the fact that it didn’t know what to do with that. But I’d prefer the tool telling me it can’t resolve that, rather than just producing a wrong result. Some of our team rely on these functionality and get bitten regularly. I’l see if i can file a bug for this.