Line breaks after SELECT but not after FROM and WHERE about plsql-formatter-settings HOT 8 CLOSED

To produce the expected result you can change the Arbori queries o6_select_before and o6_select_after like that:

o6_select_before:
      [parent) subquery
    & (
        [node) 'SELECT'
      )
    & parent < node
;

o6_select_after:
      [parent) subquery
    & (
        [node) 'SELECT'
      )
    & parent < node
;

These queries provide the node for the option SELECT/FROM/WHERE. In this case it considers only SELECT and all other keywords like WITH, INTO, FROM, WHERE, CONNECT, START, GROUP, HAVING, ORDER are ignored.

I suggest to change the Arbori program in an external editor and not in the preferences dialog.

from plsql-formatter-settings.

This is a lightweight formatter.

So, if you add a line break in one of the select terms, the result is as you expect.

If you want to enforce every select_term on a new line then have a look at the JS in the Arbory query a16_add_line_breaks_before. Especially this if statement. You might want to comment it out.

from plsql-formatter-settings.

I have not tried with 22.2.1 yet. It works with 22.2.0.

Unformatted:

select * from dual where dummy = 'X';

Formatter result:

select
   *
from
   dual
where
   dummy = 'X';

Can you please provide the input you've tried, the formatter settings you've used the effective and the expected result? Thank you.

from plsql-formatter-settings.

Thank you for the reply. This use case might not be possible with the standard SQL Developer preferences, but I just wanted to see before trying to understand the Arbori syntax.

Unformatted

select 1, 2, 3
from dual
where 1 = 1 and 2 = 2;

Formatter Results

select 1,
       2,
       3
from dual
where 1 = 1
      and 2 = 2;

Expected Results

select
  1,
  2,
  3
from dual
where 1 = 1
  and 2 = 2;

Here are my SQL Developer Advanced Format settings

from plsql-formatter-settings.

Thanks. I can reproduce your results.

With the current settings it is not possible to produce your expected result. I see two problems:

1. You do not want to have keywords right-aligned, but the and should be right-aligned with where.
   It's an unusual formatting style and not covered by the settings nor in the Arbori program. However, you can achieve what you want by adapting the Arbori program.

2. You just want to a line break after select but not after from and where.
   The option is for all keywords (e.g. also for the order by) and not just for the select. There is no option for that in SQLDev. However, it's doable, but you have to adapt the Arbori program yourself.

I hope this answers your question.

from plsql-formatter-settings.

1. You do not want to have keywords right-aligned, but the and should be right-aligned with where.
   It's an unusual formatting style and not covered by the settings nor in the Arbori program. However, you can achieve what >you want by adapting the Arbori program

Correction. It's just indented by 2 spaces, not right-aligned.

I guess it's probably the easiest to check SELECT/FROM/WHERE and write an Arbori fix-rule at the end.

from plsql-formatter-settings.

Thank you, I am not very familiar with the Arbori rules, so I will take a look. I went ahead and closed the issue.

from plsql-formatter-settings.

That is fantastic, it worked. I will play with the Arbori program to help resolve the issue with break after commas in select list. FROM and WHERE clause work with your example.

select
  1, 2, 3
from dual
where 1 = 1
  and 2 = 2;

from plsql-formatter-settings.