“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.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

13 Comments

  1. balakrishna Reply

    Hi,

    My code is like this

    CURSOR Cur_Tax IS SELECT Nvl(Pct_Un,0) Pct_Un,
    Nvl(Pct_Rc,0) Pct_Rc
    FROM T_Taxval
    WHERE Nptf = P_Nptf AND
    Noval = P_Noval AND
    A = B;

    and i want like below
    CURSOR Cur_Tax IS
    SELECT Nvl(Pct_Un,0) Pct_Un,
    Nvl(Pct_Rc,0) Pct_Rc
    FROM T_Taxval
    WHERE Nptf = P_Nptf
    AND Noval = P_Noval
    AND A = B;
    Regards,
    Sumathi.V

  2. Shira Ishikawa Reply

    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?

    • 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.

    • Vadim Tropashko

      @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.

    • sankar reddy

      hi,
      plz try below code. it may works for u r problem.
      SET PAGESIZE 200
      SET LINESIZE 140

  3. 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

    • Vadim Tropashko

      With no break on comma option it looks like a bug

    • 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.

  4. Vadim Tropashko Reply

    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

    • Vadim Tropashko

      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.

  5. 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.

    • You’ll have to use the custom formatting to make your own rules. I’ll try to take a look tomorrow…at the beach.

Reply To Shira Ishikawa Cancel Reply