OUTER APPLY doesn't honor 'Parentheses style' settings

StereoType

This is how SQL Prompt 8.2.3.2750 formats the code:

SELECT emp.EmployeeId
      ,history.WorkType1 - (history.WorkType2 + history.WorkType3)
FROM Emp.Employee emp
     OUTER APPLY
   (
      SELECT SUM(  CASE WHEN hst.WorkType = 1 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                       ELSE @ActualDate
                                                                                  END
                                                           )
                        ELSE NULL
                   END
                ) AS WorkType1
            ,SUM(  CASE WHEN hst.WorkType = 2 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                       ELSE @ActualDate
                                                                                  END
                                                           )
                        ELSE NULL
                   END
                ) AS WorkType2
            ,SUM(  CASE WHEN hst.WorkType = 3 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                       ELSE @ActualDate
                                                                                  END
                                                           )
                        ELSE NULL
                   END
                ) AS WorkType3
      FROM Emp.WorkHistory hst
   ) AS history

Can't get something close to this with style options:

SELECT emp.EmployeeId
      ,history.WorkType1 - (history.WorkType2 + history.WorkType3)
FROM   Emp.Employee emp
       OUTER APPLY(
                   SELECT SUM(CASE WHEN hst.WorkType = 1 THEN DATEDIFF(DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                ELSE @ActualDate
                                                                                           END)
                                   ELSE NULL
                              END
                             ) AS WorkType1
                         ,SUM(CASE WHEN hst.WorkType = 2 THEN DATEDIFF(DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                ELSE @ActualDate
                                                                                           END)
                                   ELSE NULL
                              END
                             ) AS WorkType2
                         ,SUM(CASE WHEN hst.WorkType = 3 THEN DATEDIFF(DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                ELSE @ActualDate
                                                                                           END)
                                   ELSE NULL
                              END
                             ) AS WorkType3
                   FROM Emp.WorkHistory hst
       ) AS history

Why extra spaces in 'SUM( CASE..' and 'DATEDIFF( DAY...' clauses?

Jessica R

Thanks for reporting this, @StereoType!

I've logged a bug for it with internal reference, SP-6667. We'll post here once we have an update on a fix.

StereoType

Hi,
do you have any news on this issue?

Jessica R

Hi @StereoType,

No update yet, I'm afraid, but I'm following up on this.

krzysztofkroczak

Hello @StereoType,

We fixed this issue in our latest version of [b]SQL Prompt <9.1.8.4871>[/b] ([url=http://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_9.1.8.4871.exe]installer here[/url]).

You can find more information about this release in [url=https://forum.red-gate.com/discussion/83138/the-latest-stable-build-of-sql-prompt-is-9-1-8-4871-18th-april]this forum post[/url].

Kind regards,
Krzysztof

StereoType

Hi,
thank you for the fix, now 'OUTER APPLY' is following the Parentheses settings, but the problem with extra leading spaces in SUM and DATEDIFF clauses is still here:

SELECT emp.EmployeeId
      ,history.WorkType1 - (history.WorkType2 + history.WorkType3)
FROM Emp.Employee AS emp
     OUTER APPLY (
                 SELECT SUM(  CASE WHEN hst.WorkType = 1 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                  ELSE @ActualDate
                                                                                             END
                                                                      )
                                   ELSE NULL
                              END
                           ) AS WorkType1
                       ,SUM(  CASE WHEN hst.WorkType = 2 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                  ELSE @ActualDate
                                                                                             END
                                                                      )
                                   ELSE NULL
                              END
                           ) AS WorkType2
                       ,SUM(  CASE WHEN hst.WorkType = 3 THEN DATEDIFF(  DAY, hst.StartDate, CASE WHEN hst.EndDate < @ActualDate THEN hst.EndDate
                                                                                                  ELSE @ActualDate
                                                                                             END
                                                                      )
                                   ELSE NULL
                              END
                           ) AS WorkType3
                 FROM Emp.WorkHistory AS hst
                 ) AS history

I have 'Expanded, simple' selected and 'Indent parentheses contents' unchecked in Parentheses settings. How to get rid of these extra spaces?

StevnRichardson

very helpful.

TomW

Hi @StereoType  

You're absolutely right, those extra spaces definitely shouldn't be there. We've logged a bug for it, internal reference SP-6927. We'll post here once we have an update on a fix.

Kind Regards,

Tom

TomW

Hi @StereoType

We've done some investigation into the issue, and we think the cause could be the "Indent List Items" setting in the formatting options. Would you be able to disable this option and see if it improves the situation?



If the issue persists, would you be able to provide your full style file so we can better reproduce and diagnose the issue?

Kind Regards,

Tom