Enhancement Proposal: Single-Line Formatting for Simple CASE WHEN Statements in SqlInform

11 Posts
2 Users
0 Likes
88 Views
(@benjamin-moeller)
Member
Joined: 2 years ago
Posts: 17
Topic starter  

I love how SqlInform allows me to format case when statements.
To make this even better, please specify that simple case when statements are formatted into one line.

Here is an example.

This sql:

select field_1 as my_field, case when field_1 = 'Snowflake' then 'Cloud' when field_2 = 'Oracle' then 'On-Premises' else 'Check' end as group_1,
case when field_1 is null then 'Empty' else field_1 end as test2 from test

 

can be automaticly formattet as:

    SELECT
                  field_1                       AS my_field
            ,
                  CASE
                         WHEN   field_1 = 'Snowflake'
                         THEN   'Cloud'
                         WHEN   field_2 = 'Oracle'
                         THEN   'On-Premises'
                         ELSE   'Check'
                  END                           AS group_1
            ,
                  CASE
                         WHEN   field_1 IS NULL
                         THEN   'Empty'
                         ELSE   field_1
                  END                           AS test2
             FROM test

 

but I would like it to be in this way:

    SELECT
                  field_1                                                                    AS my_field
            ,
                  CASE
                         WHEN   field_1 = 'Snowflake'
                         THEN   'Cloud'
                         WHEN   field_2 = 'Oracle'
                         THEN   'On-Premises'
                         ELSE   'Check'
                  END                                                                        AS group_1
            ,
                  CASE   WHEN   field_1 IS NULL   THEN   'Empty'  ELSE   field_1  END        AS test2
             FROM test

   
Quote
Guido
(@admin)
Member Admin
Joined: 2 years ago
Posts: 175
 

Hi @benjamin-moeller, what would be the rule/definition of a "simple" CASE?

Would it be the number of chars? e.g. < 80 chars?

 

Regards

Guido


   
ReplyQuote
(@benjamin-moeller)
Member
Joined: 2 years ago
Posts: 17
Topic starter  

Hi Guido

i would prefer the same logic as you implemented at the SQL Level:

image

So i can tweak the length as it fits best

Other one (if simpler) would be
if only one when condition exists and only 1 and/or condition, then use one line

like:

select
case when 1 = 1 then 'super' else 'bad' end as example, -- fits rule
case when 1 = 1 and 2 = 2 then 'super else 'bad' end as example_2, --fits rule
case 
       when  1 = 1 then 'super'
       when 2 = 2 then 'ok'
       else 'bad' 
end as example_3 -- does not fit rule
from test

Thanks
Ben


   
ReplyQuote
Guido
(@admin)
Member Admin
Joined: 2 years ago
Posts: 175
 

OK, will give it a try


   
ReplyQuote
Guido
(@admin)
Member Admin
Joined: 2 years ago
Posts: 175
 

Hi @benjamin-moeller,

grafik

Please give it a try

Regards

Guido

 


   
ReplyQuote
(@benjamin-moeller)
Member
Joined: 2 years ago
Posts: 17
Topic starter  

Hello Guido
that was quick! Thank you.

Unfortunately it doesn't work as desired yet.
Only the first case statement is formatted like this.
All others follow the ‘general’ rules defined above.


   
ReplyQuote
(@benjamin-moeller)
Member
Joined: 2 years ago
Posts: 17
Topic starter  

 

Here a Screenshot of the result

Showing also some other issues with alignment...

image
This post was modified 1 week ago 2 times by Benjamin Moeller

   
ReplyQuote
Guido
(@admin)
Member Admin
Joined: 2 years ago
Posts: 175
 

Would you mind sending me the query in text format?

Regards

Guido


   
ReplyQuote
Guido
(@admin)
Member Admin
Joined: 2 years ago
Posts: 175
 

hi @benjamin-moeller, and could you please send me your profile via mail to info@sqlinform.com


   
ReplyQuote
Guido
(@admin)
Member Admin
Joined: 2 years ago
Posts: 175
 

First 2 issues have been solved

grafik

 

For the last issue, this is more complicated. In the meantime you could use a work-around and "uncheck" the "Align Commas" Option for COALESCE


   
ReplyQuote
(@benjamin-moeller)
Member
Joined: 2 years ago
Posts: 17
Topic starter  

Hi Guido
thanks for your work.

will come back with the sql example and the file tomorrow


   
ReplyQuote
Share: