Parens + Case formatting rules

I've got this query:

Code:

UPDATE ASeqResult
SET SpkVal = [RawSpkVal]*[SpkFac]/(100-
CASE
WHEN [Pmoist] IS NULL THEN 0
ELSE [Pmoist]
END
)*100,
OmegaQual=Null,
MDL = ([rawMDL]*[DF])*([PrepFac]/[OriginalFac])*(100/(100-
CASE
WHEN [Pmoist] IS NULL THEN 0
ELSE [Pmoist]
END
))*([BackFracFac]/[DE]),
PQL = ([rawPQL]*[DF])*([PrepFac]/[OriginalFac])*(100/(100-
CASE
WHEN [Pmoist] IS NULL THEN 0
ELSE [Pmoist]
END
))*([BackFracFac]/[DE]),
BLKrefval = 0,
SPKrefval = CASE WHEN tmpARunSeq.SPKref = 0
THEN SPKrefval
ELSE 0
END,
RPDrefval = 0,
ColDiff = 0,
Backrefval = 0,
REC=0,
RPD=0
FROM
tmpARunSeq INNER JOIN ARunSeqResult ON tmpARunSeq.SeqNo = ARunSeqResult.SeqNo
WHERE (((tmpARunSeq.CalcSamp)=1) AND (tmpARunSeq.WSID = 'MYWS'))

When formatted with S.A it comes out looking like:

Code:

UPDATE ARunSeqResult
SET SpkVal = [RawSpkVal] * [SpkFac] / ( 100 - CASE WHEN [Pmoist] IS NULL THEN 0 ELSE [Pmoist] END )
* 100 ,
OmegaQual = NULL ,
MDL = ( [rawMDL] * [DF] ) * ( [PrepFac] / [OriginalFac] ) * (
100 / ( 100 - CASE WHEN [Pmoist] IS NULL THEN 0 ELSE [Pmoist] END )
) * ( [BackFracFac] / [DE] ) ,
PQL = ( [rawPQL] * [DF] ) * ( [PrepFac] / [OriginalFac] ) * (
100 / ( 100 - CASE WHEN [Pmoist] IS NULL THEN 0 ELSE [Pmoist] END )
) * ( [BackFracFac] / [DE] ) ,
BLKrefval = 0 ,
SPKrefval = CASE
WHEN tmpARunSeq.SPKref = 0 THEN SPKrefval
ELSE 0
END ,
RPDrefval = 0 ,
ColDiff = 0 ,
Backrefval = 0 ,
REC = 0 ,
RPD = 0
FROM tmpARunSeq
INNER JOIN ARunSeqResult
ON tmpARunSeq.SeqNo = ARunSeqResult.SeqNo
WHERE (
( ( tmpARunSeq.CalcSamp ) = 1 )
AND ( tmpARunSeq.WSID = 'MYWS' )
)

The case statements all end up in a single line parens (unless wrapped due to length).. which just breaks readability for me.

If I disable the "(..)" formatting structure I get something more like below (which is better)..:

Code:

UPDATE ARunSeqResult
SET SpkVal = [RawSpkVal] * [SpkFac] / ( 100 -
CASE
WHEN [Pmoist] IS NULL THEN 0
ELSE [Pmoist]
END )
* 100 ,
OmegaQual = NULL ,
MDL = ( [rawMDL] * [DF] ) * ( [PrepFac] / [OriginalFac] ) * (
100 / ( 100 -
CASE
WHEN [Pmoist] IS NULL THEN 0
ELSE [Pmoist]
END )
) * ( [BackFracFac] / [DE] ) ,
PQL = ( [rawPQL] * [DF] ) * ( [PrepFac] / [OriginalFac] ) * (
100 / ( 100 -
CASE
WHEN [Pmoist] IS NULL THEN 0
ELSE [Pmoist]
END )
) * ( [BackFracFac] / [DE] ) ,
BLKrefval = 0 ,
SPKrefval = CASE
WHEN tmpARunSeq.SPKref = 0 THEN SPKrefval
ELSE 0
END ,
RPDrefval = 0 ,
ColDiff = 0 ,
Backrefval = 0 ,
REC = 0 ,
RPD = 0
FROM tmpARunSeq
INNER JOIN ARunSeqResult
ON tmpARunSeq.SeqNo = ARunSeqResult.SeqNo
WHERE (
( ( tmpARunSeq.CalcSamp ) = 1 )
AND ( tmpARunSeq.WSID = 'MYWS' )
)

But now anything with parens such as the CTE formatting we were discussing elsewhere:

	Code:
	;WITH CTETBL AS ( SELECT * FROM foo WHERE bar = 'x')

Last edited by Mindflux on Wed May 29, 2013 10:11 am; edited 2 times in total

Anyone have any ideas?

If I paste this same code into several of the online formatters it seems to handle it quite ok.

Thought I'd bounce this up again.

:)

nevermind.... didn't work

I changed the Code Formatting > Parentheses > Text Line for In-line Parentheses to 40 and I got the following (which depends on my other customized settings as well but you might like it):

Code:

UPDATE ASeqResult
SET SpkVal = [RawSpkVal] * [SpkFac] / (
      100 - CASE
         WHEN [Pmoist] IS NULL    THEN 0
         ELSE [Pmoist]
         END
   ) * 100
,OmegaQual = NULL
,MDL = ([rawMDL] * [DF]) * ([PrepFac] / [OriginalFac]) * (
100 / (
   100 - CASE
      WHEN [Pmoist] IS NULL    THEN 0
      ELSE [Pmoist]
      END
)
) * ([BackFracFac] / [DE])
,PQL = ([rawPQL] * [DF]) * ([PrepFac] / [OriginalFac]) * (
100 / (
   100 - CASE
      WHEN [Pmoist] IS NULL    THEN 0
      ELSE [Pmoist]
      END
)
) * ([BackFracFac] / [DE])
,BLKrefval = 0
,SPKrefval = CASE
      WHEN tmpARunSeq.SPKref = 0    THEN SPKrefval
      ELSE 0
   END
,RPDrefval = 0
,ColDiff = 0
,Backrefval = 0
,REC = 0
,RPD = 0
FROM
tmpARunSeq
INNER JOIN
ARunSeqResult
   ON tmpARunSeq.SeqNo = ARunSeqResult.SeqNo
WHERE (
      ((tmpARunSeq.CalcSamp) = 1)
      AND (tmpARunSeq.WSID = 'MYWS')
   )

I also noticed the first three cases of cases :) are only there to exchange NULL value with 0. Regarding performance ISNULL/COALESCE might be a bit better (I guess). Of course, that doesn't apply to the last case.

Thanks. It's not my code originally but is isnull or coalesce really any faster than a case?

As far as the 40 char after paren, that fixed it here, but broke it other places.

I guess it's hard to get formatting that works universally.

So, it seems that ISNULL and CASE are nearly as performant as each other with COALESCE coming in a close third from what reading I did on it.

I don't think I'll change it as it's been working fine, other than being ugly syntax wise when being formatted with parens involved.

ISNULL is a special case solution for COALESCE, probably that's why it's a bit faster. And it falls out as soon as other branches are inserted into CASE.

Yes, it's hard to find a cure that suits all. Perhaps introducing formatting rule precedence/order could provide a solution here but that might not be an easy task. Nevertheless, we might see that as a feature request and let the developers figure out how to implement it :)

By the way, did you know that you can format parts of the script in the editor as well? You can format the entire script once and then select CASE statements and format them one-by-one. A bit cumbersome and applying a full-format will ruin it immediately but it works. I do that when I'm not satisfied with the formatting result as a whole.

And that brings to my mind another feature request: there's a hotkey for finding matching brackets and there's a feature that does a similar thing with BEGIN..END, CASE..END structures, namely clicking/hovering the mouse over the keyword and clicking the popup (jump to closing/opening part of the statement). Could you extend that to be able to select that statement (or contents of the brackets) at the same time? It could make the partial formatting easier as well.

gemisigo wrote:

By the way, did you know that you can format parts of the script in the editor as well? You can format the entire script once and then select CASE statements and format them one-by-one. A bit cumbersome and applying a full-format will ruin it immediately but it works. I do that when I'm not satisfied with the formatting result as a whole.

And that brings to my mind another feature request: there's a hotkey for finding matching brackets and there's a feature that does a similar thing with BEGIN..END, CASE..END structures, namely clicking/hovering the mouse over the keyword and clicking the popup (jump to closing/opening part of the statement). Could you extend that to be able to select that statement (or contents of the brackets) at the same time? It could make the partial formatting easier as well.

Yeah I knew you could format parts of the script by highlighting them and mashing Ctrl+F11. The problem is.. I'll format the case the way I want it and then do something else down below and msah Ctrl+F11 again on the whole script and screw up my casing. ha ha

I +1 the idea of matching select statement brackets... It already does this for insert into, right?

	Mindflux wrote:
	Yeah I knew you could format parts of the script by highlighting them and mashing Ctrl+F11. The problem is.. I'll format the case the way I want it and then do something else down below and msah Ctrl+F11 again on the whole script and screw up my casing. ha ha

Well, yes. I've developed the habit to only format portion of script I see :)

	Mindflux wrote:
	I +1 the idea of matching select statement brackets... It already does this for insert into, right?

Not exactly but sort of. It selects the corresponding comment in the VALUES part when you click/move your cursor into it. Also, it is setting dependent. But yes, I was thinking about something similar for brackets and statement structures as CASE, BEGIN..END, even IF..END IF in MySQL...