-
1. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
are you saying you want different formatting on a THEN in a CASE vs just an IF THEN?
Or are you asking for the ability to insert a linebreak BEFORE a THEN vs only AFTER?
-
2. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
I think this concerns not only IF, but also WHILE with LOOP, CURSOR with IS etcetera. Should the keyword opening the block be on the new line or not?
Joop
-
3. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
Joop has understood me correctly. Many programmers like:
IF 1=1 THEN
X := 1;
END IF;
But I was brought up to use this:
IF 1=1
THEN
X := 1;
END IF;
Same with LOOP. I like:
WHILE a > B
LOOP
X := 1;
END LOOP ;
It is a totally different situation with CASE. What follows THEN is a single expression, not what may be 100s of statements. Specifying line break after THEN and ELSE in the formatter I get:
BEGIN
x := CASE WHEN a > b
THEN
4
WHEN a < b
THEN
5
ELSE 6
END;
END;
No break after ELSE? This is too many breaks for me so I tried setting a line break after CASE only and I get:
BEGIN
x := CASE
WHEN a > b
THEN 4
WHEN a < b
THEN 5
ELSE 6
END;
END;
This looks pretty good but I now get:
BEGIN
IF 1 = 1 THEN x := 1;
y := 2;
END IF;
END;
Which is very peculiar and also inconsistent with CASE as the former has a line break before THEN and the latter doesn't.
-
4. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
I admit there was some confusion what exactly "break after THEN" (and some such) means. As you have demonstrated, verbatim interpretation is less than ideal. Therefore, this functionality has been amended as documented in the second part of the article
https://vadimtropashko.wordpress.com/2017/01/03/custom-formatting-in-sqldev-4-2/
walking through custom formatting of CASE expression example. This formatting is now (4.2.17.81) default. Here is your example:
BEGIN
x :=
CASE
WHEN a > b THEN 4
WHEN a < b THEN 5
ELSE 6
END;
IF 1 = 1 THEN
x := 1;
y := 2;
END IF;
END;
"Break after THEN" doesn't affect IF statement:
BEGIN
x :=
CASE
WHEN a > b THEN
4
WHEN a < b THEN
5
ELSE 6
END;
IF 1 = 1 THEN
x := 1;
y := 2;
END IF;
END;
As for your IF formatting request, the closest shape I'm able to achieve is
IF
1 = 1
THEN
x := 1;
y := 2;
END IF;
with one extra custom format condition in the "isolatedNodes" section (renamed to "simpleIndentConditions" in 4.2.17.81):
| [node) rel & [node^) if_stmt
(It actually indents the IF condition (AKA "rel"); not THEN keyword following this condition).
I'll add the advanced option for that. Hopefully it would make into the pending release...
-
5. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
Thanks Vadim
In fact, I think most old fashioned people like me are prepared to adapt to have LOOP on the same line as FOR and THEN on the same line as IF. I also think that in an IF statement making a line break after THEN/ELSE unconditional is OK. It is also OK to make a line break after LOOP unconditional. So what you have done to make the line break after THEN/ELSE be specific to CASE statements is fine for me.
However, in case you missed it, your option of allowing a line break after a CASE ELSE seems not to be working. Here is what I get with all CASE line breaks on:
BEGIN
IF CASE a
WHEN
3
THEN
4
ELSE a
END > 7 THEN
a := b;
ELSE
c := d;
e := f;
END IF;
END;
/
See the line "ELSE a"?
-
6. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
I think the more options the formatter has the better, so all for this, regardless of whether I would ever use it myself. Unfortunately the Sql Developer formatter is one area I think the product lacks behind others.
-
7. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
We've spent the entire 4.2 development cycle gathering feedback, improving the formatter engine, and making the preferences more straightforward. If you have actionable feedback, please share it.
Here's what the preferences for 4.2 will look like when it's released:
-
8. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
This was insightful example as well. The default formatting
BEGIN
IF
CASE a
WHEN 3 THEN 4
ELSE a
END
> 7 THEN
a := b;
ELSE
c := d;
e := f;
END IF;
END;
/
have two problems.
First, indenting the "a" following "THEN" requires one extra condition
| [node) pls_expr & [node-1) 'ELSE'
in custom formatting program. However, as Jeff have already mentioned, just flipping the CASE->ELSE advanced formatting option should suffice.
The second eyesore is the newly introduced indentation of the entire CASE statement produced "> 7 THEN". This is, again, fixed with fine tuning of custom formatting. The 2 conditions that fix it are:
| [node) case_expr & 