Line breaks after SELECT but not after FROM and WHERE

[vitodcampanelli]

I am using SQL Developer 22.2.1.234 and I was curious to know if it was possible to apply the following format condition.

We would like to have a line break after SELECT but not after the FROM and WHERE. The option in SQL Developer applies to all three.

If there a way to achieve this? If this is not the correct place to ask the question, then please delete the issue/question.

Thanks

[PhilippSalvisberg]

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.

[vitodcampanelli]

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

[PhilippSalvisberg]

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.

[PhilippSalvisberg]

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.

[vitodcampanelli]

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

[PhilippSalvisberg]

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.

[vitodcampanelli]

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;

[PhilippSalvisberg]

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.