August 20, 2007 at 5:48 am
I am attempting to use a case statement in my Order By clause, and have it sort by two columns. However, it seems to only be ordering the data on the first column and not the second. My code is as follows:
ORDER BY CASE WHEN @SortBy = 'S' THEN CAST(column1 AS VARCHAR) + ',' + column2 END
CASE WHEN @SortBy = 'L' THEN CAST(column3 AS VARCHAR) + ',' + column2 END
I don't know if it would make a difference, but some of the rows return NULL for the value in column1 and column3. Any thoughts?
August 20, 2007 at 6:15 am
ORDER BY
CASE @SortBy
WHEN 'S'
THEN ISNULL(CAST(column1 AS VARCHAR(255)) + ',', '') + column2
ELSE ISNULL(CAST(column3 AS VARCHAR(255)) + ',', '') + column2
END
August 20, 2007 at 6:16 am
Mark
Check your CONCAT_NULL_YIELDS_NULL setting. The default is ON, so if one of the values you are concatenating is null, you will end up with a null whatever the other strings are.
John
August 20, 2007 at 6:39 am
John, CONCAT_NULL_YIELDS_NULL is set to false (according to the Properties window for the database).
Ken, I'm getting some wierd results with your solution. When sorting by column1 and column2, the result sets lists the nulls followed by the other values, and appears to work correctly except for a single rogue null at the end of the result set. When sorting by column3 and column2, the nulls are listed at the end of the result set.
Any thoughts? Thanks for the help, guys. It's appreciated.
August 20, 2007 at 6:56 am
ORDER BY
CASE @SortBy
WHEN 'S'
THEN
CASE
WHEN column1 IS NULL AND column2 IS NULL
THEN ''
WHEN column1 IS NULL
THEN SPACE(255) + CAST(column2 AS varchar(255))-- 255 or length column1
WHEN column2 IS NULL
THEN CAST(column1 AS varchar(255))
ELSE CAST(column1 AS varchar(255)) + CAST(column2 AS varchar(255))
END
ELSE
CASE
WHEN column3 IS NULL AND column2 IS NULL
THEN ''
WHEN column3 IS NULL
THEN SPACE(255) + CAST(column2 AS varchar(255))-- 255 or length column1
WHEN column2 IS NULL
THEN CAST(column3 AS varchar(255))
ELSE CAST(column3 AS varchar(255)) + CAST(column2 AS varchar(255))
END
END
August 20, 2007 at 9:58 am
One thought - are there only 'S' and 'L' as values? Or are those just the only two that you are testing for? The ELSE clauses will capture everything not 'S', which may be more than 'L'. While the coding proposed does address your needs, they are not strictly logically equivalent.
Just another example of where ambiguity in the problem statement allows assumptions or other 'errors' to creep in. Clearly, if there are more than two values, modifying the code presented is fairly straight foward - the ELSE clause code goes under a WHEN 'L' clause, and the ELSE becomes either an error message or a nothing happens clause.
August 20, 2007 at 10:11 am
I got it to work by breaking all of the conditions down into individual statements, as follows:
CASE WHEN @SortBy = 'S' THEN column1 END
CASE WHEN @SortBy = 'L' THEN column3 END
CASE WHEN @SecondarySort = 'P' THEN column2 END
CASE WHEN @SecondarySort = 'N' THEN column4 END
This seems to have the desired effect, even if it is a little less "pretty." Thanks for all the help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply