aspose file tools*
The moose likes JDBC and the fly likes How do you indent SQL? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How do you indent SQL?" Watch "How do you indent SQL?" New topic
Author

How do you indent SQL?

Timothy Chen Allen
Ranch Hand

Joined: Mar 16, 2003
Posts: 161
Not to start a holy war or anything, but is there any published guideline on how to indent SQL so it is easier to read? Mainly I'm interested in SELECT statements, but the other stuff would be nice too.
I'm interested in the whole thing: indenting, whether keywords should be in upper or lower case, whether to indent with tabs or spaces...
I had started adopting a style like this:
SELECT foo, bar
FROM baz, bat
WHERE yuck = yam
But quickly ran into readability problems when there were many columns involved. Also I didn't take into account what to do with LEFT OUTER JOIN statements.
Thanks in advance.


Timothy Chen Allen
Learn Spanish in Washington, DC
HS Thomas
Ranch Hand

Joined: May 15, 2002
Posts: 3404
I prefer this way of [NOT] indenting; it's more readable.

The tricky part is to decide where to put brackets AND if you need them.
The above method is by far more readable than indenting heavily WITH brackets.
Indentations make one assume that the optimiser works in a particular way but usually this does not match exactly with the way SQL is written. Who knows, the optimisation changes daily depending on how volatile the data is. So most times I would prefer writing SQL, set at a time as shown above, and leave the rest to the optimiser/interpreter.

Sometimes you do neeed inner selects

Otherwise , indentations are more trouble than they are worth, IMHO.
(I haven't written SQL for a while so this may not be current practice but I am sure this method can be improved further..)HTH
regards
[ November 09, 2003: Message edited by: HS Thomas ]
Mathias Nilsson
Ranch Hand

Joined: Oct 13, 2003
Posts: 107
What do you do if there is TSQL involved with
IF..ELSE..END statements?


SCJP2 , MCP( 70-229 ) , Preparing For SCWDC
HS Thomas
Ranch Hand

Joined: May 15, 2002
Posts: 3404
I haven't used T-SQL. Here is a forum that may help T-SQL Programming Part 1 - Defining Variables, and IF...ELSE logic
.
I have used PL/SQL which looks similar. I don't like having processing logic embedded within database calls or vice-versa. Doing this imposes limits on the scope for reusability. Still try and follow the same indentation rules I gave but refactor the SQL into another method if possible.
So how could you re-write this code ?

Hmmmm. While this example doesn't look messy with simple select count(*) statements it soon will with more complicated SQL. I would standardise on putting SQL in new methods / files that are easily traceable in the Development environment.Indented SQL looks like spaghetti after a while ; so personally I'd avoid indenting heavily.
regards
[ November 09, 2003: Message edited by: HS Thomas ]
Dolfandave Uyemura
Ranch Hand

Joined: Jun 11, 2002
Posts: 31
I have done some T-SQL in a data migration project. I think the indentation was something like:
code:
--------------------------------------------------------------------------------
SELECT foo, bar
FROM baz, bat
WHERE yuck = yamma mamma AND
chuck = bananarama;
LEFT OUTER JOIN

SELECT foo, bar
FROM baz, bat
WHERE chuck = yamma mamma
AND (baz.yuck = bananarama OR bat.yuck = yamma mamma);
--------------------------------------------------------------------------------
Good Luck,
Dolfandave
Dolfandave Uyemura
Ranch Hand

Joined: Jun 11, 2002
Posts: 31
The last post didn't hold my indents. I have tried again below. Also, use a font like Courier New that is "monospaced font" so each character is exactly one space and always use spaces instead of tabbing. Tabs represent only ONE space so that can really throw you off.
code:
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Dolfandave

Code tags will help you.-- MdQ
[ November 15, 2003: Message edited by: Marilyn de Queiroz ]
Dolfandave Uyemura
Ranch Hand

Joined: Jun 11, 2002
Posts: 31
Still not holding form, here is a link I created and uploaded.

http://www.geocities.com/dave_uyemura/javaranch.html

Dolfandave
Timothy Lovern
Greenhorn

Joined: Nov 15, 2003
Posts: 4
for what it's worth:

Keywords get their own lines
columns and conditions get their own lines, with a level of indentation greater than the keywords.

This gets adjusted for complex queries or joins, but it is easy to read and very simple to add additional columns and or conditions to later.
[ November 15, 2003: Message edited by: Timothy Lovern ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How do you indent SQL?
 
Similar Threads
resource file - add quotes around param
How to trace SQL statements ?
Executing a batch of SQL commands
Formatting Java Code
JDBC Connections Freezing with Multiple Users Accessing DB