CASE 中的排序依据和不同类型 [英] Order by and Different Types in a CASE
问题描述
我需要根据参数中的整数输入对结果集进行排序.
I have a requirement to order a result set by a column, based on an Integer input into a parameter.
问题是,我需要为 OrderBy 使用 CASE,而且代码似乎接受 case 列中的第一个TYPE"......任何其他类型都失败.
Problem is, I need to use a CASE for the OrderBy, and it seems the code accepts the first 'TYPE' in the case column... any other types fail.
我的代码是这样的:
WITH error_table AS
(
SELECT Row_Number() OVER
(ORDER BY
CASE @orderBy
WHEN 1 THEN received_date -- Last Rx'd message
WHEN 2 THEN message_id -- Message Id
WHEN 3 THEN zibmat.short_name -- Message action type
WHEN 4 THEN error_action.short_name -- Status type
WHEN 5 THEN ime.[allocated_date] -- Allocated Date
ELSE received_date
END) AS RowNumber
,ime.[ijis_message_error_id]
,ime.[message_id]
,ime.[message_version]
因此,当 OrderBy 为 1 时,它会起作用.它按 rx_date 排序...但是当我向它发送 2 时,它因数据时间转换错误而失败.
So, when OrderBy is 1, it works. It sorts by rx_date... but when I sent it a 2, it fails with a data time conversion error.
看起来所有类型都必须相同...
It looks like all the types must be the same...
发送 5 可以正常工作,因为那也是约会时间.
Sending a 5 works fine, as that's a date time too.
有什么办法可以解决这个问题吗?
Is there a way I can fix this?
推荐答案
CASE 语句只能解析为一种数据类型.这与您知道@orderby 只会选择一个分支并且它将是特定数据类型这一事实无关.
A CASE statement must resolve to only one data type. This is regardless of the fact that you know that @orderby will choose only one branch and it will be a particular data type.
你可以使用这样的东西,虽然笨重但可以工作.
You could use something like this, which would be clunky but will work.
ORDER BY
CASE @orderBy WHEN 1 THEN received_date -- Last Rx'd message
WHEN 2 THEN 0
WHEN 3 THEN 0
WHEN 4 THEN 0
WHEN 5 THEN ime.[allocated_date] -- Allocated Date
ELSE received_date END,
CASE @orderBy WHEN 1 THEN 0
WHEN 2 THEN message_id -- Message Id
WHEN 3 THEN 0
WHEN 4 THEN 0
WHEN 5 THEN 0
ELSE 0 END,
CASE @orderBy WHEN 1 THEN ''
WHEN 2 THEN ''
WHEN 3 THEN zibmat.short_name -- Message action type
WHEN 4 THEN error_action.short_name -- Status type
WHEN 5 THEN ''
ELSE '' END
这篇关于CASE 中的排序依据和不同类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!