9 Replies Latest reply on Mar 28, 2017 8:30 AM by John_K

    4.2EA2 SQL Formatter IF THEN ELSE suggestion

    3417476

      Although the "line breaks" section in the SQL Formatter "advanced" tab has a section dedicated to CASE it does not have one for IF / THEN / ELSE and indeed it seems that the

      setting for THEN in a CASE expression affects IF / THEN / ELSE as well.

       

      Deciding whether THEN should appear on the same line as IF is, in my experience, perhaps the single most emotive topic in code formatting in all languages and I would suggest an easy way for people to choose.

       

      --Tony

        • 1. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
          thatJeffSmith-Oracle

          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
            user13430807

            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
              3417476

              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
                Vadim Tropashko-Oracle

                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
                  3417476

                  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
                    John_K

                    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
                      thatJeffSmith-Oracle

                      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:

                       

                      formatter-42-options.png

                      • 8. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
                        Vadim Tropashko-Oracle

                        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 & ![node^) pls_expr   

                         

                        | [node) pls_expr & [node^) nonblock_compound_stmt 

                         

                        The first condition narrows indenting case expression not to be a part of a larger condition, such as "CASE ... END > 7" in the above example. The second condition instructs to indent this entire larger condition. The output:

                         

                        BEGIN

                            IF

                                CASE a

                                    WHEN 3 THEN 4

                                    ELSE a

                                END > 7

                            THEN

                                a := b;

                            ELSE

                                c := d;

                                e := f;

                            END IF;

                        END;

                        /

                         

                        In general, the newly redesigned for 4.2 format feature allows for Arbori formatting program to evolve even after the official release.

                        • 9. Re: 4.2EA2 SQL Formatter IF THEN ELSE suggestion
                          John_K

                          Looks like a lot more options have been added.

                           

                          Initcap keywords would be nice if they don't already exist, and a wider range of options for lists (column names, function parameters etc) - stack up to n, otherwise newline them all etc.