7 Replies Latest reply on Aug 22, 2017 1:03 AM by Skip - Absolute

    SQL Formatter: avoid indentation before ANSI joins and AND clauses

    Felipe_SF

      The SQL formatter is doing the following which I'm trying to avoid:

      - Indenting ANSI joins

      - Adding a line break before the closing parenthesis of the ON clause for ANSI joins

      - Indenting AND clauses

       

      It used to work fine in version 4.1. Is there a way to get the same formatting in 4.2?

       

      Sample Query:

      SELECT col1

      , col2 from tab1 t1 inner join tab2 t2

      on (t1.col3=t2.col3) where t1.col4=t2.col4 and t1.col5=t2.col5;

       

      Version 4.1.5.21 Build MAIN-21.78

      SELECT col1,

             col2

      FROM tab1 t1

      INNER JOIN tab2 t2 ON(t1.col3 = t2.col3)

      WHERE t1.col4 = t2.col4

      AND t1.col5 = t2.col5;

       

      Version 4.2.0.17.089 Build 17.089.1709

      SELECT col1,

             col2

      FROM tab1 t1

          INNER JOIN tab2 t2 ON ( t1.col3 = t2.col3

          )

      WHERE t1.col4 = t2.col4

          AND t1.col5 = t2.col5;

       

        • 1. Re: SQL Formatter: avoid indentation before ANSI joins and AND clauses
          Vadim Tropashko-Oracle

          Here is what I was able to do with custom format in 17.3:

           

          SELECT col1,

              col2

          FROM tab1 t1

          INNER JOIN tab2 t2 ON ( t1.col3 = t2.col3 )

          WHERE t1.col4 = t2.col4

          AND t1.col5 = t2.col5;

           

          This is with default advanced format options; all the customization is in arbori rules on custom page.

           

          Unfortunately, the incoming 17.2 release is locked and it doesn't support set of arbori rules for line breaks (those are line breaks which are not part of indentations).

           

          Here is the best can be done in  4.2&17.2:

           

          SELECT col1,

                 col2

          FROM tab1 t1

              INNER JOIN tab2 t2 ON ( t1.col3 = t2.col3 )

          WHERE t1.col4 = t2.col4

          AND t1.col5 = t2.col5;

           

          with advanced options:

           

          breaksAfterSelect = false 

          breaksAfterFrom = false 

          breaksAfterWhere = false

          breaksAroundLogicalConjunctions = Before

           

          and custom format:

           

          | [node) condition  &  [node-1) '(' & ![node^^) on_using_condition

          | [node) condition  &  [node^)  compound_condition & ![node) compound_condition & ![node^^) on_using_condition

          | [node) condition  &  [node^) having_clause & ![node) compound_condition

          | [node) condition  &  [node^) hierarchical_query_clause & ![node) compound_condition

          | [node) condition  &  [node^) on_using_condition & ![node) compound_condition

          --| [node) condition  &  [node^) where_clause --& ![node) compound_condition

          • 2. Re: SQL Formatter: avoid indentation before ANSI joins and AND clauses
            B.Delmée

            The sqldeveloper program manager once specifically said that "preserving pre-4.2 formatter behaviour was never a criterion".

            I find this unfortunate as I happened to like the old result better in most cases, too.

            I am not questioning that the new formater is more solid and flexible, but currently it exposes too few parameters to even reproduce what we had, which i would have considered a reasonable expectation from users.

            And as i already mentioned in other threads, "custom rules" are not realistic to expect a normal IDE user to learn.

            For another similar example of what i'd like to achieve but cannot under 4.2, see here.

            • 3. Re: SQL Formatter: avoid indentation before ANSI joins and AND clauses
              thatJeffSmith-Oracle

              We can easily add options going forward. That wasn't feasible before.

               

              Your feedback helps drive what those options will be.

               

              Overwhelming feedback on the old formatting was that it wasn't good enough. We're addressing use cases as they come up.

              • 4. Re: SQL Formatter: avoid indentation before ANSI joins and AND clauses
                B.Delmée

                Sorry to the OP, replying here since my own related thread has been archived.

                 

                It does not seem like version 17.2 exposes any new parameter that would allow getting the formating requested in this thread (nor my older one)

                 

                4.217.2
                • 5. Re: SQL Formatter: avoid indentation before ANSI joins and AND clauses
                  Vadim Tropashko-Oracle

                  This is correct, there was 17.2 code freeze happened month ago, so that this thread didn't influence the release.

                   

                  I'm reorganizing formatting rules, but still don't see any compelling reasons for introducing new options. I would accommodate "no break on ANSII join ON clause", but see no reason to keep the old "break on ANSII joins" option. At first glance, "break on ANSII joins" == false makes sence

                   

                  select ename, dname, emp.deptno, dept.deptno

                  from SCOTT.EMP RIGHT outer join SCOTT.DEPT

                  on emp.deptno = dept.deptno

                   

                  However, what is the expected action on the subsequent joins? Here is the formatting that is on its way into 17.3:

                   

                  SELECT t.table_name,

                         c.column_id,

                         c.column_name,

                         k.comments

                  FROM user_tables t

                       INNER JOIN user_tab_cols c ON t.table_name = c.table_name

                       LEFT JOIN user_col_comments k ON k.table_name = t.table_name AND  k.column_name = c.column_name

                  • 6. Re: SQL Formatter: avoid indentation before ANSI joins and AND clauses
                    B.Delmée

                    Thanks Vadim, that explains.

                     

                    But now that you have got this better engine in place, let's try and make it friendlier to use (to me "custom rules" are not friendly and last-resort only)

                     

                    We have been asked for examples of what we'd like to have during the 4.2 test cycle, and some of us provided that feedback

                    There is not much point in discussing the specifics as this is by definition a matter of preferences.

                    You have one example in this here ticket, and another in my older post from January, neither of whcih can currently we obtained.

                    In these two cases we do not want to indent joins, for starters.

                    • 7. Re: SQL Formatter: avoid indentation before ANSI joins and AND clauses
                      Skip - Absolute

                      I recently adopted 4.2, and after a few weeks figured out that the ctrl-F7 was totally blowing out my old formatting rules.  Something I counted on in prior releases suddenly stopped working, and did things totally unexpected.  After discovering the difference in the Preference settings, I still can't seem to get the new settings to work the way I'd like. 

                       

                      I agree that an IDE user does not want to learn another language just to format code.  So until more options that work similar to 4.1 are introduced in 4.2 builds, I conclude I have to format by hand, and hope I'm consistent - or learn to like the 'tall' code.  Unfortunately, my colleagues will hate reading what I write.

                       

                      It's great that Jeff is asking for use cases, and I see how I could submit them, but my time budget doesn't permit.  It seems there is a reasonably small subset of keywords, like SELECT, WHEN, AND, CASE, THEN, the IFs and ELSEs, where each could be controlled with a few options for each.  Indent before or not; indent after or not; line feed before or not; line feed after or not.  Then add controls for parentheses bracketing.  Yeah, I know I'm oversimplifying the problem.  

                       

                      I also will consider keeping 4.1 for formatting, but that is quite a 'workaround'.

                       

                      This might be an area where having an SQL Developer beta thread where it's possible to download the key files that support the formatter RULES, without needing to reload the entire product, would be helpful.  Beta users could submit use cases with each release.  Perhaps in a few months great strides could be made.

                       

                      Skip