Indentation mising on Pivot Tables FOR

Davidian

This indentation on the FOR command is missing for pivot tables. 
This isn't obvious if the pivot is your only command but even wrapping it in an BEGIN and END you can see how the rest of the command is indented but the FOR is not

When you have more complex procs this indentation is heavily noticeable but either way it is problematic. 

Other than "remember to check pivots each time you KY" is there something that can be done?

Jon_Kirkwood

Hi @Davidian

Thank you for reaching out on the Redgate forums regarding your indentation concern.

Are you able to share a sample SQL statement that highlights this unexpected behaviour.

I would like to replicate this in my test environment as close as your working example & escalate through to our developers to look further into this formatting behaviour.

Davidian

sure.. I mean this is just an extract with some begins and the like to show you the indentation effects applied upon a  CTRL+K+Y - there's obviously more to the procs we use but it happens in all circumstances and this should suffice to demonstrate the issue - screenshot attached as well. 

<br><div>BEGIN</div><div>BEGIN TRY</div><div>IF 1 = 1</div><div>BEGIN</div><div>SELECT [ValuationDate] = [Date],</div><div>&nbsp; &nbsp;FulcrumEntityId,</div><div>&nbsp; &nbsp;Parent_Portfolio,</div><div>&nbsp; &nbsp;Parent_PortfolioId,</div><div>&nbsp; &nbsp;[TotalFundValue] = [Total Fund Value],</div><div>&nbsp; &nbsp;[AggregatesValue] = [Aggregates],</div><div>&nbsp; &nbsp;AggregatesVsTotalFundPercent = [Aggregates] / NULLIF([Total Fund Value], 0)</div><div>&nbsp; INTO #tblPivot</div><div>&nbsp; FROM (SELECT FulcrumEntityId, Parent_Portfolio, Parent_PortfolioId, PortfolioType, [Date], Valuation FROM #tblAggregateValue) UFV</div><div>&nbsp; PIVOT (SUM(Valuation)</div><div>FOR PortfolioType IN ([Total Fund Value], [Aggregates])) PivotData;</div><div>END;</div><div>END TRY</div><div>BEGIN CATCH</div><div>PRINT 'Error';</div><div>END CATCH;</div><div>END;</div><div><br></div>

Jon_Kirkwood

Thank you for the example script & image.

 My first thought is perhaps it may be the collapsing of parentheses occurring here. Can you please confirm if enabling/disabling this setting makes changes to your FOR statement within the parentheses

 

 Formatting Styles > Global > Parentheses > Collapse parentheses if contents are shorter than <x> characters

 

 

Davidian

Ok, so, sort of - I have that one set to 180

turning it off sorts that FOR statement, but also this:


becomes:



So it seems like there is no way to balance having both

The closest from here seems to be if I change List items from always to if longer than wrap column 


however other statements like:

become


Which is sort of the reverse of what I am going for with those ones lol. 

I will have to keep playing.