Oracle Formatting Options - JOIN direct under FROM

Toad for SQL Server
1

Hello,

I can nowhere find an option how I can define that the JOIN’s are directly formatted like the from or where. The JOIN in a new line is just allignet right by the FROM.

I would like it so:

SELECT table1.id, table2.id, table3.id, table4.id
FROM table1
JOIN table2 on table2.id = table1.id
LEFT JOIN table3
NATURAL JOIN table4;

I dont like it so:

SELECT table1.id, table2.id
FROM table1
JOIN table2 on table2.id = table1.id
LEFT JOIN table3
NATURAL JOIN table4;

regards Ralf Sommer

2

Hello Ralf,

The left hand column of keywords (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY etc.) represent distinct clauses of the statement. Adding the JOIN, LEFT and NATURAL keywords (and probably some others as well) would make it a mixed list of everything. As it stands now the right hand side of the FROM is exactly the table reference list in all its simplicity or complexity.

Having said that, there is a notable exception : multiple AND or OR operators in the WHERE clause:

SELECT table1.id, table2.id
FROM table1
WHERE id3 = id4
AND field3 > field3
AND field5 = field6;

I guess some prefer this for cosmetic reasons. But AND and OR are little keywords.

How should we align the join operators? Something like this perhaps:

  SELECT table1.id, table2.id, table3.id, table4.id
    FROM table1
    JOIN table2 ON table2.id = table1.id

LEFT JOIN table3 ON table3.id = table1.id
NATURAL JOIN table4;

It’s just when statements become complex (many and complex clauses) that design constraints tend to conflict… And I wonder if it does help to make the statement more understandable?

I welcome suggestions whatsoever.

Thanks,
Andre

3

Hi,

so I wrote in the first post would be enough I guess. Its like the formatting option in PLSQL Developer from alroundautomations. The where clause in your example looks good for me. OR-keyword could be little bit different, because it is different.

SELECT table1.id,
table2.id,
table3.id,
table4.id
FROM table1
JOIN table2 ON table2.id = table1.id
LEFT JOIN table3 ON table3.id = table1.id
NATURAL JOIN table4
WHERE id3 = id4
AND field3 > field3
AND (field5 = field6 OR
field6 IS NULL);

I would say a rule could be all keywords under FROM and over WHERE are be left aligned to FROM-keyword, except the ON-keyword.

A second rule could be under where all AND keywords are right aligned and all OR-keywords trailing the line.

I know its all individual taste.

Regards Ralf

PS: Sorry for my not so perfect perfect.

4

You are talking about preference vs functionality.

Under tool -> Option -> Choose editor and reformat the statement listed in the form you prefer. This should solve your problem.

From: Andre Vergison [mailto:bounce-avergison@toadworld.com]

Sent: Friday, January 26, 2018 11:39 AM

To: toadss@toadworld.com

Subject: RE: [Toad for SQL Server - Discussion Forum] Oracle Formatting Options - JOIN direct under FROM

RE: Oracle Formatting Options - JOIN direct under FROM

Reply by Andre Vergison

Hello Ralf,

The left hand column of keywords (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY etc.) represent distinct clauses of the statement. Adding the JOIN, LEFT and NATURAL keywords (and probably some others as well) would make it a mixed list of everything. As it stands now the right hand side of the FROM is exactly the table reference list in all its simplicity or complexity.

Having said that, there is a notable exception : multiple AND or OR operators in the WHERE clause:

SELECT table1.id, table2.id

FROM table1

WHERE id3 = id4

AND field3 > field3

AND field5 = field6;

I guess some prefer this for cosmetic reasons. But AND and OR are little keywords.

How should we align the join operators? Something like this perhaps:

  SELECT table1.id, table2.id, table3.id, table4.id

    FROM table1

    JOIN table2 ON table2.id = table1.id

LEFT JOIN table3 ON table3.id = table1.id

NATURAL JOIN table4;

It’s just when statements become complex (many and complex clauses) that design constraints tend to conflict… And I wonder if it does help to make the statement more understandable?

I welcome suggestions whatsoever.

Thanks,

Andre

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for SQL Server Forum notifications altogether.

Toad for SQL Server - Discussion Forum

Flag
this post as spam/abuse.

5

Ok - then give me please a tipp how I can setup this - in this way I like - I couldn’t found this option yet.

Thank You

6

Hi,

You told me I can setup it. Can you help me out, what options I have to change?

regards Ralf