# Issue with Formatting Window Functions

**URL:** <https://forums.toadworld.com/t/issue-with-formatting-window-functions/19320>\
**Category:** Toad for SQL Server\
**Created:** [June 28, 2013, 6:17am UTC](https://forums.toadworld.com/t/issue-with-formatting-window-functions/19320 "2013-06-28T06:17:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![DavidStein](https://avatars.discourse-cdn.com/v4/letter/d/cab0a1/32.png) [@DavidStein](https://forums.toadworld.com/u/DavidStein)\
**Post date:** [June 28, 2013, 6:17am UTC](https://forums.toadworld.com/t/issue-with-formatting-window-functions/19320/1 "2013-06-28T06:17:43Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Julia.A.Trevgoda2013](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/julia.a.trevgoda2013/32/187_2.png) [@Julia.A.Trevgoda2013](https://forums.toadworld.com/u/Julia.A.Trevgoda2013)\
**Post date:** [June 28, 2013, 7:31am UTC](https://forums.toadworld.com/t/issue-with-formatting-window-functions/19320/2 "2013-06-28T07:31:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![avergison](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/avergison/32/66_2.png) [@avergison](https://forums.toadworld.com/u/avergison)\
**Post date:** [June 28, 2013, 8:47am UTC](https://forums.toadworld.com/t/issue-with-formatting-window-functions/19320/3 "2013-06-28T08:47:47Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![DavidStein](https://avatars.discourse-cdn.com/v4/letter/d/cab0a1/32.png) [@DavidStein](https://forums.toadworld.com/u/DavidStein)\
**Post date:** [June 28, 2013, 9:16am UTC](https://forums.toadworld.com/t/issue-with-formatting-window-functions/19320/4 "2013-06-28T09:16:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Julia.A.Trevgoda2013](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/julia.a.trevgoda2013/32/187_2.png) [@Julia.A.Trevgoda2013](https://forums.toadworld.com/u/Julia.A.Trevgoda2013)\
**Post date:** [July 1, 2013, 2:47am UTC](https://forums.toadworld.com/t/issue-with-formatting-window-functions/19320/5 "2013-07-01T02:47:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![avergison](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/avergison/32/66_2.png) [@avergison](https://forums.toadworld.com/u/avergison)\
**Post date:** [July 9, 2013, 9:28am UTC](https://forums.toadworld.com/t/issue-with-formatting-window-functions/19320/6 "2013-07-09T09:28:41Z")

</div>

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