May 3, 2018 at 12:31 pm
Hi,
In a view, there’s a use of the Max() function which is confusing.
As part of a Select, there’s
MAX(CASE WHEN rline = 1 THEN pe.LNAME + ', ' + pe.FNAME END) AS Person1Name
I don’t understand what this Max is supposed to return. Syntactically, is looks like it’s returning the Maximum value of the Person’s name.
Does anyone understand this, or do I need to post more detail?
rline is the returned Row_Number() of a Partition By/Order By statement. It's always either 1 or 2.
May 3, 2018 at 12:40 pm
This a common technique ("trick") to get a single line of values from multiple lines of input. It's almost always at least a pair of conditions, e.g.:
MAX(CASE WHEN rline = 1 THEN pe.LNAME + ', ' + pe.FNAME END) AS Person1Name,
MAX(CASE WHEN rline = 2 THEN pe.LNAME + ', ' + pe.FNAME END) AS Person2Name,
First row, rline is 1:
Person1 is set to "pe.LNAME + ', ' + pe.FNAME".
Person2 is set to NULL (since rline = 2 was not true, and the default ELSE value is NULL).
Next row, rline is 2:
Person1 stays the same, since the NULL is not the MAX() value.
Person2 becomes "pe.LNAME + ', ' + pe.FNAME", since that is larger than the NULL value it was before.
And so on.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 3, 2018 at 1:01 pm
ScottPletcher - Thursday, May 3, 2018 12:40 PMThis a common technique ("trick") to get a single line of values from multiple lines of input. It's almost always at least a pair of conditions, e.g.:
MAX(CASE WHEN rline = 1 THEN pe.LNAME + ', ' + pe.FNAME END) AS Person1Name,
MAX(CASE WHEN rline = 2 THEN pe.LNAME + ', ' + pe.FNAME END) AS Person2Name,First row, rline is 1:
Person1 is set to "pe.LNAME + ', ' + pe.FNAME".
Person2 is set to NULL (since rline = 2 was not true, and the default ELSE value is NULL).Next row, rline is 2:
Person1 stays the same, since the NULL is not the MAX() value.
Person2 becomes "pe.LNAME + ', ' + pe.FNAME", since that is larger than the NULL value it was before.And so on.
" and the default ELSE value is NULL"
That make sense, ok thanks Scott.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply