# Convert to ANSI join syntax

**URL:** <https://forums.toadworld.com/t/convert-to-ansi-join-syntax/25717>\
**Category:** Toad for Oracle - Beta\
**Created:** [July 11, 2013, 6:54am UTC](https://forums.toadworld.com/t/convert-to-ansi-join-syntax/25717 "2013-07-11T06:54:11Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![roddyt](https://avatars.discourse-cdn.com/v4/letter/r/3be4f8/32.png) [@roddyt](https://forums.toadworld.com/u/roddyt)\
**Post date:** [July 11, 2013, 6:54am UTC](https://forums.toadworld.com/t/convert-to-ansi-join-syntax/25717/1 "2013-07-11T06:54:11Z")

</div>

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);

---

<div class="post-metadata">

**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\
**Post date:** [July 11, 2013, 8:15am UTC](https://forums.toadworld.com/t/convert-to-ansi-join-syntax/25717/2 "2013-07-11T08:15:05Z")

</div>

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](http://gyk.r.mailjet.com/redirect/vfczhmtclvb9nk8717bhr6/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/19813.aspx)**

> 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](http://gyk.r.mailjet.com/redirect/kcbntz0cx6634xhbmbdr8j/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/19813.aspx?subscribe=false) on this subject.
> 
> Or [  
> Unsubscribe from Toad for Oracle - Beta](http://gyk.r.mailjet.com/redirect/rm7ft12q4zdry9wffknjt7/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86.aspx?subscribe=false) notifications altogether.
> 
> Toad for Oracle - Beta Discussion Forum

> [Flag](http://gyk.r.mailjet.com/redirect/l8syhjocvs2og3mor2x6th/www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/19813.aspx?AbuseContentId=ce13055e-f57a-4a45-a7db-2b90339b0338&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true)  
> this post as spam/abuse.

> ![](https://global.discourse-cdn.com/quest/original/3X/5/0/50b30fd5f87c85fe5cba2635cb83316ca71250d7.gif)