Convert to ANSI join syntax

Toad for Oracle - Beta
1

I grabbed a recent query to try the new “Convert to ANSI join syntax” feature. The before and after queries are below. I have two observations. I have table aliases on all columns, but the formatter stripped them from the columns where there is no discrepancy. They may not be necessary for execution, but I like to have them to immediately identify what table they belong to.

The other thing is that the schema name was prepended to the tables. This isn’t good for me as I sometimes have to run the same SQL in different schemas.

select p.projid, p.document_no, p.orgid, p.shortname,p.boe_method, e1.lastname||’, ‘||e1.firstname boe_preparer,
e2.lastname||’, ‘||e2.firstname original_bidder, e3.lastname||’, ‘||e3.firstname last_bidder,
p.last_mod_date, p.boe_app_disapp, e4.lastname||’, '||e4.firstname boe_approver, p.boe_app_disapp_date
from task_org_v p, BRT_IDS_2013_PMER_7_9_13 m, emp e1, emp e2, emp e3, emp e4
where p.projid=m.projid
and e1.empid = p.boe_preparer
and e2.empid = p.original_bidder
and e3.empid = p.last_bidder
and e4.empid = p.boe_approver;

SELECT P.PROJID,
DOCUMENT_NO,
P.ORGID,
SHORTNAME,
BOE_METHOD,
E1.LASTNAME || ', ’ || E1.FIRSTNAME BOE_PREPARER,
E2.LASTNAME || ', ’ || E2.FIRSTNAME ORIGINAL_BIDDER,
E3.LASTNAME || ', ’ || E3.FIRSTNAME LAST_BIDDER,
LAST_MOD_DATE,
BOE_APP_DISAPP,
E4.LASTNAME || ', ’ || E4.FIRSTNAME BOE_APPROVER,
BOE_APP_DISAPP_DATE
FROM SBT.TASK_ORG_V P
INNER JOIN SBT.BRT_IDS_2013_PMER_7_9_13 M ON (P.PROJID = M.PROJID)
INNER JOIN SBT.EMP E1 ON (E1.EMPID = BOE_PREPARER)
INNER JOIN SBT.EMP E2 ON (E2.EMPID = ORIGINAL_BIDDER)
INNER JOIN SBT.EMP E3 ON (E3.EMPID = LAST_BIDDER)
INNER JOIN SBT.EMP E4 ON (E4.EMPID = BOE_APPROVER);

2

Thanks. I don't have an answer for you at the moment, but I've passed this information along and will let you know when I have something.

Michael

On 07/11/2013 09:54 AM, roddyt wrote:

Convert to ANSI join syntax

Thread created by roddyt
I grabbed a recent query to try the new "Convert to ANSI join syntax" feature. The before and after queries are below. I have two observations. I have table aliases on all columns, but the formatter stripped them from the columns where there is no discrepancy.
They may not be necessary for execution, but I like to have them to immediately identify what table they belong to.

The other thing is that the schema name was prepended to the tables. This isn't good for me as I sometimes have to run the same SQL in different schemas.

select p.projid, p.document_no, p.orgid, p.shortname,p.boe_method, e1.lastname||', '||e1.firstname boe_preparer,

e2.lastname||', '||e2.firstname original_bidder, e3.lastname||', '||e3.firstname last_bidder,

p.last_mod_date, p.boe_app_disapp, e4.lastname||', '||e4.firstname boe_approver, p.boe_app_disapp_date

from task_org_v p, BRT_IDS_2013_PMER_7_9_13 m, emp e1, emp e2, emp e3, emp e4

where p.projid=m.projid

and e1.empid = p.boe_preparer

and e2.empid = p.original_bidder

and e3.empid = p.last_bidder

and e4.empid = p.boe_approver;

SELECT P.PROJID,

   DOCUMENT_NO,

   P.ORGID,

   SHORTNAME,

   BOE_METHOD,

   E1.LASTNAME || ', ' || E1.FIRSTNAME BOE_PREPARER,

   E2.LASTNAME || ', ' || E2.FIRSTNAME ORIGINAL_BIDDER,

   E3.LASTNAME || ', ' || E3.FIRSTNAME LAST_BIDDER,

   LAST_MOD_DATE,

   BOE_APP_DISAPP,

   E4.LASTNAME || ', ' || E4.FIRSTNAME BOE_APPROVER,

   BOE_APP_DISAPP_DATE

FROM SBT.TASK_ORG_V P

   INNER JOIN SBT.BRT_IDS_2013_PMER_7_9_13 M ON (P.PROJID = M.PROJID)

   INNER JOIN SBT.EMP E1 ON (E1.EMPID = BOE_PREPARER)

   INNER JOIN SBT.EMP E2 ON (E2.EMPID = ORIGINAL_BIDDER)

   INNER JOIN SBT.EMP E3 ON (E3.EMPID = LAST_BIDDER)

   INNER JOIN SBT.EMP E4 ON (E4.EMPID = BOE_APPROVER);

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.