convert oracle SQL to ANSII standard?

Toad for Oracle
1

Can TOAD 10.6 do it?

2

For Joins yes.

There’s an action for that on the DB MISC tab – ANSI JOIN SYNTAX

3

Hi Jeff,

I found the way to convert it using Query Builder. ( I was unable to find a DB
MISC tab as you suggested).

Unfortunately it turned this …

SELECT al1.affiliatekey,

   al5.companyname,

   al4.field4,

   al4.field8,

   al7.programname,

   al6.programkey,

   al6.commission,

   al4.field7,

   al6.trxid,

   al8.orderid,

   al9.comments,

   al6.datewentpayable,

   al9.ajustmentkey,

   al6.trxcode2tcode,

   al8.totalprice,

   al4.field5,

   al6.datewenthold,

   al1.username,

   al10.codename,

   al9.adjustmentcode2tcode,

   al9.updatedby2tuser

FROM qsn_app.taffiliate al1,

   qsn_app.tpayout al2,

   qsn_app.taccount al3,

   qsn_app. taffiliatetag al4,

   qsn_app.tcontact al5,

   com_app.ttransaction al6,

   com_app.tprogram al7,

   com_app.tsale al8,

   com_app.tadjustment al9,

   qsn_app.tcode al10

WHERE ( al4.affiliatekey2taffiliate(+) = al1.affiliatekey

    AND al2.account2tacount(+) = al6.accountkey

    AND al6.programkey = al7.programkey(+)

    AND al8.trxid(+) = al6.trxid

    AND al6.trxid = al9.trxid(+)

    AND al3.accountkey = al2.account2tacount

    AND al3.affiliate2taffiliate = al1.affiliatekey

    AND al2.payto2tcontact = al5.contactkey

    AND al1.payment_method2tcode = al10.codekey)

   AND (    al6.datewenthold > '15-Mar-2011'

        AND (NOT al6.trxcode2tcode = 1171)

        AND al1.affiliatekey IN (103622, 20529980, 20831411,

20897505));

into this …

SELECT al1.affiliatekey,

   al5.companyname,

   al4.field4,

   al4.field8,

   al7.programname,

   al6.programkey,

   al6.commission,

   al4.field7,

   al6.trxid,

   al8.orderid,

   al9.comments,

   al6.datewentpayable,

   al9.ajustmentkey,

   al6.trxcode2tcode,

   al8.totalprice,

   al4.field5,

   al6.datewenthold,

   al1.username,

   al10.codename,

   al9.adjustmentcode2tcode,

   al9.updatedby2tuser

FROM taccount al3

   JOIN taffiliate al1

      ON (al3.affiliate2taffiliate = al1.affiliatekey)

   JOIN tpayout al2

      ON (al3.accountkey = al2.account2tacount)

   JOIN tcontact al5

      ON (al2.payto2tcontact = al5.contactkey)

   JOIN tcode al10

      ON (al1.payment_method2tcode = al10.codekey)

   RIGHT OUTER JOIN taffiliate al1

      ON (al4.affiliatekey2taffiliate = al1.affiliatekey)

   RIGHT OUTER JOIN com_app.ttransaction al6

      ON (al2.account2tacount = al6.accountkey)

   LEFT OUTER JOIN com_app.tprogram al7

      ON (al6.programkey = al7.programkey)

   RIGHT OUTER JOIN com_app.ttransaction al6

      ON (al8.trxid = al6.trxid)

   LEFT OUTER JOIN com_app.tadjustment al9

      ON (al6.trxid = al9.trxid)

WHERE (al6.datewenthold > ‘15-Mar-2011’)

   AND (al1.affiliatekey IN (103622, 20529980, 20831411, 20897505));

Note that taffiliatetag al4 is a table that occurs in the first query but
not the second one.

Hmmmmm?

Regards,

Mike

On Tue, Mar 29, 2011 at 5:10 PM, Jeff Smith wrote:

For Joins yes.

 

There’s an action for that on the DB MISC tab – ANSI JOIN SYNTAX
4

Query Builder is being rewritten from scratch for Toad 11.0 and intends to
address numerous more complex issues like this – and similar issues with
its reverse engineer /read script to build query. It’s in the beta right
now, but we’re kind of mid way done – but feedback such as this with
the beta would be very helpful in the very near future J

5

Thanks guys,

looking forward to the new Query Builder. I’m sure it will be cooked
with awesome sauce.

Regards,

Mike

On Wed, Mar 30, 2011 at 4:42 AM, Bert Scalzo wrote:

Query Builder is being rewritten from scratch for Toad 11.0 and intends to
address numerous more complex issues like this – and similar issues with
its reverse engineer /read script to build query. It’s in the beta right
now, but we’re kind of mid way done – but feedback such as this with the
beta would be very helpful in the very near future J