And…? Formatting in Version 4.2

thatjeffsmith SQL Developer 12 Comments

Tell Others About This Story:

“I do not want a line break after the and statement.”

I’ve seen this come up a few times, so let’s answer that right now.

SELECT 1 FROM dual
WHERE 1=2 AND
2=3 AND 3=4

Default formatting changes that to

SELECT 1
FROM dual
WHERE 1 = 2
    AND
        2 = 3
    AND
        3 = 4

This is NOT what the person asking for help wants.

To the Preferences!

Boolean connector….an AND or OR for example

So let’s change that and see what happens.

And…

Or…

Ta-da.

Speaking of OR

Don’t use my code, it’s worthless.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 12

  1. I have similar problem, but with after commas. Example below:

    nvl(
    oeh.cust_po_number,
    ‘-‘
    )

    Formatter on 4.2 didn’t ignore commas inside the NVL, while in 4.1.5 it did great even when i ops after comma line break. Another problem is the indent, subquery not indented correctly, resulting with Select indented but the From and Where not indented. And another again is the UNION/UNION ALL, the formatter doesn’t have any option to add line break here.

    Any solution for this? Or is there any way to import format setting from 4.1.5?

    1. thatjeffsmith Post
      Author

      How do you mean the sub query isn’t indented properly?

      SELECT 1
      FROM dual
      WHERE 1 IN (
              SELECT 1
              FROM dual
          );

      Please always provide a test case if you want me to look at your formatter issues.

    2. @Shira

      I don’t see any problem with UNION. For excessive line breaks
      https://community.oracle.com/thread/4040444
      add the following line to the custom format
      & [closestAncestorDescendent.ancestor+10 < closestAncestorDescendent.ancestor)
      This makes function calls with small number of arguments, such as NVL, inline.

  2. Question. If you change the test code to “SELECT 1,2” and reformat, how come the FROM now moves to the first line? When I reformat, I get this:

    SELECT 1,2 FROM dual
    WHERE 1 = 2
    AND 2 = 3
    AND 3 = 4

    1. thatjeffsmith Post
      Author
        1. Sorry Vadim, I should have added that small IMPORTANT fact in my question. I have no break on comma option set in my preferences. As soon as I add at least one comma(,) on the first line, the FROM clause will always move up to the SELECT line.

  3. Comment out
    –| [node) expr & [ancestor) aggregate_function
    in the ancestorDescendant rule. Alternatively, you can exclude only listagg like this:
    | [node) expr & [ancestor) aggregate_function & ![ancestor) listagg

    1. To expand my comment a little, somewhat more detailed discussion of excessive line breaks is here:
      https://community.oracle.com/thread/4040444
      The suggested amendment, that is the condition
      & [closestAncestorDescendent.ancestor+10 < closestAncestorDescendent.ancestor)
      is incorporated into official code. As it has been discussed in the thread, if function argument is long, they would be broken into separate lines; if the list is short, then they would be inline. Unfortunately, listagg syntax is tricky, and it escapes this condition. For this case please use my earlier suggestion.

  4. I have a similar preference for not breaking after open paren “(” or before a close paren “)” . An open paren should never end a line and an close paren should never start a line. I may be one only one that likes code this way be a formatter should be able to generate any layout that is syntactically correct.

    Example

    SELECT set#
    , LISTAGG(
    val
    ,','
    ) WITHIN GROUP( ORDER BY val )
    FROM sets
    GROUP BY
    set#


    SELECT set#
    , LISTAGG( val, ',' ) WITHIN GROUP( ORDER BY val )
    FROM sets
    GROUP BY set#

    Or maybe I have not figured out how the make the auto-detect learn my formatting.

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *