Issue with Formatting Window Functions

Toad for SQL Server
1

Consider the following simple query:

Select
CustomerHistoryId
,CustomerNum
,CustomerName,LAG(Planet) over(Partition by CustomerNum Order by ChangeDate ) PrevPlanet
From Source.Customer
Where CustomerNum = 2
Order by ChangeDate;

The Formatter mangles any Window Functions, and I can’t figure out how to “fix” it by customizing the settings.

For example, the above typically becomes something like this:

SELECT CustomerHistoryId
,CustomerNum
,CustomerName
,LAG(Planet) over

( Partition by CustomerNum Order by ChangeDate ) PrevPlanet
From Source.Customer
Where CustomerNum = 2
Order by ChangeDate;

Is this something I can “fix” by customizing settings or is it something the developers would have to address?

Also, how do I insert code into this form so that the formatting is preserved?

2

Hi David,

Thank you very much for this finding. It’s really a bug with analytic functions parsing.

I’ve created CR#110555 to correct it.

regards, Julia.

3

David,

Could you paste the formatter options you’re using? Go to the formatter options top pane and press Copy Options to Clipboard.

Formatting with default options yields a reasonable result, see below. (text may be wrapped by this board)

Thanks,
Andre

  SELECT CustomerHistoryId,
         CustomerNum,
         CustomerName,
         LAG (Planet) OVER (PARTITION BY CustomerNum ORDER BY ChangeDate)
            PrevPlanet
    FROM Source.Customer
   WHERE CustomerNum = 2
ORDER BY ChangeDate;
4

When using the Original Defaults which may be from Toad 5.X and ended up in 6 from upgrades, it looks like this:

SELECT CustomerHistoryId,

CustomerNum,

CustomerName,

LAG (Planet) OVER

( PARTITION BY CustomerNum ORDER BY ChangeDate)

PrevPlanet

FROM Source.Customer

WHERE CustomerNum = 2

ORDER BY ChangeDate;

Formatter options are below:

252

5.252.0

5.252

99

1

0

5

Andre, I also was able to replicate this behaviour with default options.

6

David,

We don’t seem to parse this Sql Server 2012 syntax correctly, hence formatting will not work.

We’ll fix that asap.

Thanks,

Andre