Case statement in SQL Rownumber () Order By does not work with varchar and int values
Why doesn't it work?
DECLARE @temp table
(ShipNo int,
Supplier varchar(10)
)
INSERT INTO @temp VALUES (1,'CFA')
INSERT INTO @temp VALUES (1, 'TFA')
INSERT INTO @temp VALUES (2, 'LRA')
INSERT INTO @temp VALUES (2, 'LRB')
INSERT INTO @temp VALUES (3, 'ABC')
INSERT INTO @temp VALUES (4, 'TFA')
Declare @OrderBy varchar(255)
sET @OrderBy = 'Supplier'
SELECT ROW_NUMBER() OVER (ORDER BY
CASE @OrderBy
WHEN 'Supplier' THEN Supplier
WHEN 'ShipNo' THEN ShipNo
END
) AS RowNo,
ShipNo,
Supplier
FROM @temp
But if you pass ShipNo to varchar in the Case expression, does it work?
0
source to share
2 answers
I know this is an old post, but this is for anyone who comes across this problem and is looking for a solution:
SELECT ROW_NUMBER() OVER (ORDER BY
CASE @OrderBy
WHEN 'Supplier' THEN Supplier
END
CASE @OrderBy
WHEN 'ShipNo' THEN ShipNo
END
)
basically you put each field in its own case. Do this only when their datatype or value inside the field is different for both columns, or when you get an error like
Error while trying to convert int to varchar or varchar to int ..
+4
source to share
From books on the internet:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
"The else_result_expression data types and any result_expression must be the same or must be an implicit conversion."
Thus, the supplier and the ShipNo must be of the same data type.
+1
source to share