Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Analytic function on single line when auto-formatting

User_1871Mar 17 2023 — edited Apr 2 2023

Regarding the auto-formatting functionality in SQL Developer (right-click SQL >> Format):

I've configured the Advanced Format preferences so that auto-formatted queries look like this:

SELECT *
 FROM ( SELECT cities.*,
               ROW_NUMBER() OVER(
   PARTITION BY country
    ORDER BY population DESC
) rn,
               COUNT(*) OVER(
   PARTITION BY country
) ct_cities
          FROM cities
) t
WHERE t.rn = 1
  AND t.ct_cities > 1;

Screenshot of preferences

That's a good start. But what I actually want is something like this:

SELECT *
 FROM (SELECT cities.*,
              ROW_NUMBER() OVER(PARTITION BY country ORDER BY population DESC) rn,
              COUNT(*)     OVER(PARTITION BY country) ct_cities
         FROM cities
      ) t
WHERE t.rn = 1
  AND t.ct_cities > 1

The main difference is the analytic/window functions. The auto-formatted analytic functions have been split up over multiple lines.

               ROW_NUMBER() OVER(
   PARTITION BY country
    ORDER BY population DESC
) rn,

Whereas I want the analytic function all on a single line:

              ROW_NUMBER() OVER(PARTITION BY country ORDER BY population DESC) rn,

Is there a way to auto-format queries so that analytic functions are kept intact on a single line?

Thanks.

Comments
Post Details
Added on Mar 17 2023
1 comment
185 views