August 15, 2013 at 2:54 pm
Hello Everyone,
I'm pretty sure that this is possible but I am stuck. I am trying to write a query that sorts based on a couple variables... This query works perfectly:
--WORKING
DECLARE @Sort1 varchar(10)='val3';
DECLARE @Sort2 varchar(10)='val2';
DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 varchar(10));
INSERT @x VALUES ('xxx','ccc','1a'),('yyy','bbb','5a'),('zzz','aaa','2a'),
('xxx','ccc','5a'),('yyy','bbb','5a')
SELECT * FROM @x
ORDER BY CASE @Sort1
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN val3
END,
CASE @Sort2
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN val3
END
The issue is that val3 needs to be an int. In the updated sample below the query works if @Sort1 = 'val3'. If @sort='val1' or 'val2' the query will fail with the error: 'Conversion failed when converting the varchar value 'ccc' to data type int.' I can get it to work if I change WHEN 'val3' THEN val3 to WHEN 'val3' THEN CAST(val3 AS varchar(10)) but then it will sort it as a string (1,11,2,21,22,3,4,etc...) which is not what I need.
DECLARE @Sort1 varchar(10)='val2'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1
--DECLARE @Sort1 varchar(10)='val3';
DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 int);
INSERT @x VALUES ('xxx','ccc',1),('yyy','bbb',5),('zzz','aaa',11),
('xxx','ccc',5),('yyy','bbb',5)
SELECT * FROM @x
ORDER BY CASE @Sort1
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN val3
END
I have been CASTing and CTEing by brains out with no luck. Any help would be appreciated. Thanks!
-- Itzik Ben-Gan 2001
August 15, 2013 at 3:01 pm
Actually, is not that difficult but sometimes we get stuck on the simplest things. 🙂
Change this code as desired
DECLARE @Sort1 varchar(10)='val3'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1
--DECLARE @Sort1 varchar(10)='val3';
DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 int);
INSERT @x VALUES ('xxx','ccc',1),('yyy','bbb',5),('zzz','aaa',11),
('xxx','ccc',5),('yyy','bbb',5)
SELECT * FROM @x
ORDER BY CASE @Sort1
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN RIGHT( REPLICATE('0', 10) + CAST( val3 AS varchar(10)), 10)
END
August 15, 2013 at 3:28 pm
Thanks Luis!
-- Itzik Ben-Gan 2001
August 19, 2013 at 12:12 am
My aversion to Carpal Tunnel insists that I suggest this:
DECLARE @Sort1 varchar(10)='val3'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1
--DECLARE @Sort1 varchar(10)='val3';
DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 int);
INSERT @x VALUES ('xxx','ccc',1),('yyy','bbb',5),('zzz','aaa',11),
('xxx','ccc',5),('yyy','bbb',5)
SELECT * FROM @x
ORDER BY CASE @Sort1
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN RIGHT( 10000000000+val3, 10)
END
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 19, 2013 at 2:53 pm
Slightly shorter
DECLARE @Sort1 varchar(10)='val3'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1
--DECLARE @Sort1 varchar(10)='val3';
DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 int);
INSERT @x VALUES ('xxx','ccc',1),('yyy','bbb',5),('zzz','aaa',11),
('xxx','ccc',5),('yyy','bbb',5)
SELECT * FROM @x
ORDER BY CASE @Sort1
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN STR(val3, 10)
END
STR produces a right-aligned space-padded string the length of the second argument.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 19, 2013 at 6:32 pm
drew.allen (8/19/2013)
Slightly shorter
DECLARE @Sort1 varchar(10)='val3'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1
--DECLARE @Sort1 varchar(10)='val3';
DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3 int);
INSERT @x VALUES ('xxx','ccc',1),('yyy','bbb',5),('zzz','aaa',11),
('xxx','ccc',5),('yyy','bbb',5)
SELECT * FROM @x
ORDER BY CASE @Sort1
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN STR(val3, 10)
END
STR produces a right-aligned space-padded string the length of the second argument.
Drew
My Carpal Tunnel thanks you Drew!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply