[FORMATTING] when set Indentation style to "Tabular, Right", it doesn't format "case when" well.

[AndyGeng123]

Standard style for "case when" is correct, but "Tabular Right" get incorrect format. I tested it on demo site.

select
  x,
  case  1 then 'a' else 'none'  end a,
  case  2 then 'b' else 'none'  end b
from andy.t_test
where id < 5;

Actual Output

   select x,
          case
                    1 then 'a'
                         else 'none'
          end a,
          case
                    2 then 'b'
                         else 'none'
          end b
     from andy.t_test
    where id < 5;

[nene]

Sorry for not responding earlier. It seems like you're using incorrect CASE expression syntax. Although you mention "when" inside the title, you don't use that keyword in the example code. At least according to SQL standard, CASE expression syntax should be one of the following:

CASE expr
  [WHEN expr THEN expr]...
  [ELSE expr]
END

CASE
  [WHEN condition THEN expr]...
  [ELSE expr]
END

I'm wondering whether you're perhaps using some SQL dialect that supports CASE-expressions without WHEN-keywords.

Anyway, the problem definitely is there, as even with WHEN-keywords the indentation is pretty bad:

   select x,
          case
                    x
                         when 1 then 'a'
                         else 'none'
          end a
     from tbl;

A good result would probably be something like:

   select x,
               case x
               when 1 then 'a'
               else 'none'
                end a
     from tbl;

or

   select x,
          case x
               when 1 then 'a'
               else 'none'
          end a
     from tbl;

Not sure. I don't really have a well-formed opinion of how these tabular formatting styles should really ideally look.

[AndyGeng123]

Hi, Oracle database have two case syntax: In online test website, If set "Indentation Style" to "Standard", format is well, But if set to "Tabular Right" or "Tabular Left", case have extra spaces.

Here is a demo, https://sql-formatter-org.github.io/sql-formatter/ select case when object_id > 500 then 'a' else 'none_a' end as col1, case object_id when 1 then 'a' when 2 then 'b' else 'none_b' end as col2 from dba_objects where rownum < 5;

[nene]

Yep. I do understand the problem. (Thanks for also mentioning the SQL flavor you're using.)

It's just that I'm not really sure what the ideal solution would be.

[AndyGeng123]

Hi, There is another online format website, after set "Stacked align" to "Align right", it can format "case" well. Just FYI: https://www.dpriver.com/pp/sqlformat.htm Many thanks for your great work!

[nene]

The formatting has now been improved in 10.8.0-beta.1