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) A socialist is someone who will give you the shirt off *someone else's* back.
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