OUTER APPLY doesn't honor 'Parentheses style' settings

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?

StereoType October 25, 2017 06:55

Comments

8 comments

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.

Jessica R October 31, 2017 17:50

0
Hi,
do you have any news on this issue?

StereoType March 27, 2018 07:49

0
Hi @StereoType,

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

Jessica R March 27, 2018 15:42

0
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

krzysztofkroczak April 18, 2018 15:17

0

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?

StereoType April 20, 2018 02:43

very helpful.

StevnRichardson April 20, 2018 11:14

0
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 April 24, 2018 09:55

0
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

TomW April 27, 2018 12:27

0

Add comment

Please sign in to leave a comment.

How can we help you today?

OUTER APPLY doesn't honor 'Parentheses style' settings

Comments

Add comment