January 12, 2009 at 9:41 am
Hi,
I got this problem:
DECLARE @meh table (
code varchar(10)
, lot_type int
)
INSERT INTO @meh (code, lot_type)
SELECT '1' , 1
UNION SELECT '8-3', 3
UNION SELECT '2' , 1
UNION SELECT '44-1', 3
UNION SELECT '3' , 1
UNION SELECT '6L' , 2
UNION SELECT '101A' , 1
UNION SELECT '34L' , 2
UNION SELECT '1B' , 1
UNION SELECT '100A' , 1
UNION SELECT '7-3', 3
UNION SELECT '2B' , 1
UNION SELECT '80L' , 2
UNION SELECT '7-1', 3
UNION SELECT '8-2', 3
UNION SELECT '2A' , 1
UNION SELECT '45-2', 3
UNION SELECT '81L' , 2
UNION SELECT '11' , 1
UNION SELECT '5L' , 2
UNION SELECT '7-4', 3
UNION SELECT '80R' , 2
UNION SELECT '12' , 1
UNION SELECT '34R' , 2
UNION SELECT '3A' , 1
UNION SELECT '45-1', 3
UNION SELECT '5R' , 2
UNION SELECT '1A' , 1
UNION SELECT '45-3', 3
UNION SELECT '8-1', 3
UNION SELECT '45-4', 3
UNION SELECT '10A' , 1
UNION SELECT '6R' , 2
UNION SELECT '11A' , 1
UNION SELECT '81R' , 2
UNION SELECT '44-2', 3
UNION SELECT '10' , 1
UNION SELECT '44-3', 3
UNION SELECT '45-5', 3
UNION SELECT '7-2', 3
UNION SELECT '45-6', 3
SELECT *
FROM @meh
ORDER BY LOT_TYPE,
(CASE
WHEN LOT_TYPE = 1 THEN RIGHT(CODE, PATINDEX('%[0-9]%', REVERSE(CODE)) - 1)
WHEN LOT_TYPE = 2 THEN CONVERT(int, SUBSTRING(CODE, 1, PATINDEX('%[^0-9]%', SUBSTRING(CODE, 1, 200) + '|') - 1))
ELSE CODE
END),
(CASE
WHEN LOT_TYPE = 1 THEN CONVERT(int, SUBSTRING(CODE, 1, PATINDEX('%[^0-9]%', SUBSTRING(CODE, 1, 200) + '|') - 1))
WHEN LOT_TYPE = 2 THEN RIGHT(CODE, PATINDEX('%[0-9]%', REVERSE(CODE)) - 1)
END)
when LOT_TYPE = 2, I like it order in this way:
5L, 5R, 6L, 6R, 34L, 34R, 80L, 80R, ...
so I add 2 case of LOT_TYPE = 2 in ORDER BY
then I got this error messag:
Msg 245, Level 16, State 1, Line 49
Conversion failed when converting the varchar value 'A' to data type int.
I change the query, still same error message:
SELECT *
FROM (SELECT *,
RIGHT(CODE, PATINDEX('%[0-9]%', REVERSE(CODE)) - 1) AS AAA,
CONVERT(int, SUBSTRING(CODE, 1, PATINDEX('%[^0-9]%', SUBSTRING(CODE, 1, 200) + '|') - 1)) AS BBB
FROM @meh) AS [derived_values]
ORDER BY LOT_TYPE,
(CASE
WHEN LOT_TYPE = 1 THEN AAA
WHEN LOT_TYPE = 2 THEN BBB
ELSE CODE
END),
(CASE
WHEN LOT_TYPE = 1 THEN BBB
WHEN LOT_TYPE = 2 THEN AAA
END)
What am I doing wrong?
Thank you for your time.
Wes
January 12, 2009 at 10:49 am
Its because in the case statement all the return values must be of the same datatype.
January 12, 2009 at 12:28 pm
Thank you for your time. Then how I can make this sort work?
Thank again
Wes
January 12, 2009 at 2:22 pm
is it possible for you to post what the expected output is suppose to be. That will help with resolution
---- [font="Tahoma"]Live and Let Live![/font] ----
January 12, 2009 at 2:50 pm
The expected result for the test data should be:
1, 2, 3, 10, 11, 12, 1A, 2A, 3A, 10A, 11A, 100A, 101A, 1B, 2B, 80L, 80R, 81L, 81R, 44-1, 44-2, 44-3, 45-1, 45-2, 45-3, 45-4, 45-6
Thank you,
Wes
January 13, 2009 at 6:42 am
Hi
Assuming that:
1. The codes that have letters in them will only contain 1 letter.
2. The codes that contain the "-" character only contains one of them.
The following select statement should work for you :
SELECT *
FROM @meh
ORDER BY LOT_TYPE,
CASE WHEN PATINDEX('%[A-Z]%',code) > 0 THEN --Test for letters
'B' + RIGHT(code, PATINDEX('%[0-9]%', REVERSE(code)) - 1)
WHEN CHARINDEX('-',code,1) > 0 THEN --Test for '-'
'ZZ'
ELSE 'A'
END,
CASE WHEN PATINDEX('%[A-Z]%',code) > 0 THEN --Test for letters
CAST(LEFT(code,PATINDEX('%[A-Z]%',code)-1) AS FLOAT)
WHEN CHARINDEX('-',code,1) > 0 THEN --Test for '-'
CAST(REPLACE(code,'-','.') AS FLOAT)
ELSE CAST(code AS FLOAT)
END
Hope that helps.
Ciao
Christo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply