Home » Other » Suggestions & Feedback » Formatting SQL
| Formatting SQL [message #622823] |
Sun, 31 August 2014 08:54  |
 |
DrabJay
Messages: 32
Registered: May 2013
|
Member |
|
|
Recently Michel suggested that I format some code I posted using SQL Formatter.
I appreciate that well-formatted code is an advantage so looked around the Forums as to how[/bold] the code should be formatted. I read the How to use code tags and make your code easier to read sticky and looked at example of formatted code from some of the more regular posting member in the forum. The note did not really expand on how the code should be formatted i.e. what the forum prefers for capitalization, line lengths, line breaks, etc. And looking at formatted code from other regular posters quite a wide variety of styles have been employed, even between different posts from the same member.
May I suggest that the note How to use code tags and make your code easier to read is expanded to lay out at least some detail as to what the forum would prefer to see in a well-formatted post and perhaps what formatting elements the forum is less concerned about.
|
|
|
|
|
|
| Re: Formatting SQL [message #622830 is a reply to message #622827] |
Sun, 31 August 2014 09:47  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I believe the dpriver sql formatter takes care of formatting, indentation, case of keywords etc. After you format your code, all you need is to enclose it in code tags before posting.
Also a good practise is to preview your message before posting it. As most of the times I have seen that OP has formatted the code with proper indentation, however, without code tags everything is lost. What else do you think is required? I don't see any issue, at least not as of now.
|
|
|
|
| Re: Formatting SQL [message #622834 is a reply to message #622830] |
Sun, 31 August 2014 10:29 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As far as I'm concerned, any formatting is better than no formatting. I don't care about different formatting styles as they depend on a tool someone uses. Manually formatted code is perfectly OK; online formatters have their own rules, TOAD's formatter it own, SQL Developer's another ones and so on. To make things worse, Forms' formatter uses <Tab> character which indents code well when you are in Forms, but the same code pasted here is malformed and indentation is lost. That's why previewing your code helps.
I don't see any difference if you post "select ... from" and not "SELECT ... FROM". Furthermore, simple code snippet that contains 2-3 lines can be read without formatting; it is proportional font (used in every message you post by default) that makes "problems"; non-proportional fonts (like Courier) are easier to read when it is about code - that's why we prefer formatted code and use of [code] tags.
If you take care of the way you post your messages, other forum members will have less problems in reading them and, hopefully, helping you solve your problem.
|
|
|
|
| Re: Formatting SQL [message #622837 is a reply to message #622823] |
Sun, 31 August 2014 10:41 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Keep your lines of code in 80 character width maximum so we can see it in a whole without having to move the eyes and/or horizontally scroll.
Don't forget to indent and align the code. When you indent PL/SQL code, indentation does not need to be of 8 or 10 characters (which leads to long lines), 2 or 3 are sufficient.
Also avoid lines with only a couple of characters (but when it adds something to the understanding).
is better and easier to read and grasp than
above all in a long query or code as the latter will lead to vertically scrolling.
So there is a balance between line density and height density. Do not put many consecutive empty lines.
A code which leads to less eyes move and/or scrolling is better (when smartly split).
Comma at the beginning or the end of the line does not matter.
Using upper or lower case does not really matter, if you have time you can put all keywords in upper case or all custom names (or... as you want), anyway it is better to use only lower or only upper case than to more or less randomly mix the upper and lower cases.
In your previous post:
least(duration, greatest(120 - nvl(sum(duration) OVER (PARTITION BY callingno, substr(calldate, 1, 8) ORDER BY calldate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 0)) * 2
would be better as easier to read and understand with something like:
2 * LEAST(duration,
GREATEST(120
- NVL(SUM(duration) OVER
(PARTITION BY callingno, substr(calldate, 1, 8)
ORDER BY calldate
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
0),
0))
Also remove all code that is in comment, it does not add anything to it but noise.
Just my opinion but this is an opinion from someone who programs since 1978.
|
|
|
|
|
|
| Re: Formatting SQL [message #622839 is a reply to message #622837] |
Sun, 31 August 2014 10:57 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
A good thought I got after looking Michel's explanation. For cases like this :
least(duration, greatest(120 - nvl(sum(duration) OVER (PARTITION BY callingno, substr(calldate, 1, 8) ORDER BY calldate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 0)) * 2
Perhaps, a formatter tool cannot format automatically, all you need to do is just add the required pseudo words to let the formatter do its job, and then just copy paste the required part of the code.
OraFAQ is a discussion forum and not a code development platform to stick to specific formatting standards. Usually, different organizations have their own code development standards for their application/product. But to implement it here in OraFAQ, would bother even more, isn't it? Therefore, the dpriver formatting technique should suffice for the sake of asking a question in a forum.
|
|
|
|
| Re: Formatting SQL [message #622841 is a reply to message #622839] |
Sun, 31 August 2014 11:07 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
LF said,
Quote:
TOAD's formatter it own, SQL Developer's another ones and so on
Yes, of course.
To add more... Not to forget that such tools also let us customize the default settings, if not too much, but at least to a certain extent. For example,
1. start a new line with 3 spaces of indentation.
2. Consider new line after every ";"
3. Consider ";" as end if line thus any next character should move to next line.
4. Move to next line after 80 characters in current line.
And so on...
@OP, I have a point in the PL/SQL Developer settings sticky in client tools forum, dedicated to code formatting. You can have a look.
|
|
|
|
| Re: Formatting SQL [message #622842 is a reply to message #622839] |
Sun, 31 August 2014 11:13 |
|
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL Formatter is not always good and need sometimes to be manually adapt.
The previous expression (converted to a query) will give the following with the default parameters:
SELECT Least(duration, Greatest(120 - Nvl(SUM(duration)
over (
PARTITION BY callingno, Substr(
calldate,
1, 8)
ORDER BY calldate ROWS BETWEEN
unbounded
preceding AND 1
preceding
), 0), 0)) * 2
FROM tab
which is, in my opinion, awful.
Quote:But to implement it here in OraFAQ, would bother even more, isn't it?
I disagree because:
1/ we need to understand the code so to get a quick answer it needs to be read painlessly
2/ there is no need to have a difference between the code you have to produce for your boss and the one to post. Both have the same purpose: to be easy to be read, understood and debugged
3/ in the end, it is a good exercise to post clear ad clean code in forum and it can only help you in your job.
|
|
|
|
| Re: Formatting SQL [message #622847 is a reply to message #622842] |
Sun, 31 August 2014 11:59 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I don't deny Michel. If we want, we can go beyond the points that we have mentioned till now. The point is not "what", but "how". Perhaps, that's the reason we have the online sql formatter to take care of the "how" part.
OP's suggestion is about levying specific formatting rules and make it general in the forum. I find it a daunting task and not possible to implement in general posting guidelines. At least, the online sql formatter provides the basic formatting for most of the posts.
If you guys agree, I can provide a file which could integrate with the formatter code in OraFAQ. That's what we did sometime ago when we had to implement a common code formatting guideline and integrate it with a client tool for code development. But, is it feasible here? We have already seen the OraFAQ's own formatter getting disabled due to impact on site's performance. And even if we had it now, I am not sure, but it might too had to implement some logic.
So bottomline, for most of the posts, online formatter does its job. But for specific cases as quoted above, I have no clue, and I don't deny too with your points where you disagreed, not at all. But still the question is how? Perhaps the answer is, there is no online tool to automate it, but the OP is responsible to take care of it manually as and when required. And we all do it as developers in our daily work.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Formatting SQL [message #622854 is a reply to message #622853] |
Sun, 31 August 2014 14:28 |
|
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:I do not think my formatting is "not easy to read" and "not nice"
Long lines are not easy to read.
Split long lines are not nice and are hard to understand.
Just see the difference between your long line and the expression I then posted. With this latter I can see at a glance what it does, with your line it is not possible for me, I have to split it (but as I am too lazy, as many other readers, I didn't even read it when you posted it).
"Nice" is indeed not a scientific measure but I think we can see if a code is nice or not in the sentence "have you some pleasure to see and read it or do you want to skip it?", I don't know for how long you program but I immediately see if a code is "nice" or not before reading it. It is the same thing than for a painting, a landscape, a chess game... we can see if it is "nice" or not but we can't defined by rules what is "nice". Maybe you do not program for long enough to feel that for code.
As I said, code should be read at a glance.
My teachers always said a procedure should not be greater than one page (and at this time the programming languages didn't accept a line larger than 80 characters, too bad there is no more such limit in the current ones).
There can't be strict guidelines as, as I said, some prefer comma at the beginning of the line and others at the end; some prefer keywords in upper case and others prefer it is for not keywords but for custom names and so on.
If you follow the guidelines I gave with the examples to make understand then the code would be OK, there no need of more detailed and definitive rules.
And, for your last sentence, very few read this topic (Hox to use code tags...) and the forum rules so we kept them very short. Some time ago there was a more detailed guide (you can still see in the current one if you develop the spoiler) and no one read it as too long.
|
|
|
|
| Re: Formatting SQL [message #622858 is a reply to message #622854] |
Sun, 31 August 2014 15:22 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I wouldn't be too greedy. If 50% of forum members
a) format code (I'd leave it to their own preferences)
b) enclose it into [code] tags
that would be wonderful!
You can't expect people to follow any "rules" regarding formatting (number of indentation characters, capital letters, whatever) as it is simply unrealistic. As Michel said: if code is nice, everyone is happy.
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 14:20:20 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
