[Solved] analytical functions and line b

6 Posts
2 Users
0 Reactions
115 Views
(@george)
Member
Joined: 2 years ago
Posts: 5
Topic starter  

Hi,

I am having problems with the analytical functions/window queries formatting.

for a statement like this

SELECT
   a.COL1                                                         AS COL1
 , a.COL2                                                         AS COL2
 , RANK() OVER (PARTITION BY a.COL1 ORDER BY a.COL3 ASC)          AS COL3
 , FIRST_VALUR() OVER (PARTITION BY a.COL2 ORDER BY a.COL3 ASC)   AS COL4
 , LEAD() OVER (PARTITION BY a.COL2 ORDER BY a.COL3 ASC)          AS COL5
FROM db.schm.tbl a
;

I am unable to get the formatting right (as its formatted now is how I would ideally like it)

However at the moment I can't seem to effect any changes, it always comes out like this

 , RANK() OVER
      (
      PARTITION BY a.COL1 ORDER BY a.COL3 ASC
      )
   AS COL3

with a line break before and after the opening/closing bracket

I only recently upgraded my version, and I don't believe this was an issue with the version I was previously using (unfortunately I don't have the machine it was installed on anymore to double check)
and I am not sure if that's was default settings or I just liked how it looked out of the box.

Current version: 6.25.1.29   (Desktop)

 Am I missing a setting somewhere?
If not is it possible to add in formatting options analytical functions please?

** extra unrelated note, I believe the tool tip for [ SP-> Parms -> Before ")" ] may be incorrect as it reads with an "="

 


   
Quote
Guido
(@admin)
Member Admin
Joined: 4 years ago
Posts: 239
 

Hi @george,

you are right. Yesterday I published a new release and changed a few settings for the RANK() OVER function.

Which DB are you working with?

As a work-around you could define the "OVER" Keyword in the My-Function list as shown in the screen shot

I will have to re-work the analytical functions

grafik

   
ReplyQuote
(@george)
Member
Joined: 2 years ago
Posts: 5
Topic starter  

That does the trick, thank you!

I am primarily working with Snowflake at the moment. 

When I went through I noticed a number of added tweaks since my last update, over the years I have tried a large number of formatters, yours is by far the best I have used, thank you for all the configuration options 🙂

 


   
ReplyQuote
Guido
(@admin)
Member Admin
Joined: 4 years ago
Posts: 239
 

Thanks!

Will add a few formatting options for the analytic. functions during week-end (see screenshot)

grafik

 

Whenever you are in some DB forums, it would be of great help when you mention SQLinForm. Google put my site far down in the result list. I assume that it is not very popular in the forums.

Thanks

Guido


   
ReplyQuote
Guido
(@admin)
Member Admin
Joined: 4 years ago
Posts: 239
 

The new Release 6.25.01.30 is ready for download

grafik

   
ReplyQuote
(@george)
Member
Joined: 2 years ago
Posts: 5
Topic starter  

This works perfectly! Thankyou 🙂
And will do on the DB forum end 🙂


   
ReplyQuote
Share: