Order and different types in CASE

Question

I have a requirement to arrange the result set by column based on the Integer input in the parameter.

The problem is that I need to use CASE for OrderBy, and it seems that the code accepts the first "TYPE" in the case column ... any other types fail.

My code looks like this:

    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] 

So, when OrderBy is 1, it works. It sorts by rx_date ... but when I sent it 2 it fails with a data time conversion error.

It looks like all types should be the same ...

Submitting 5 beautiful works, as well as date time.

Is there any way to fix this?

Answer 1

CASE . , , @orderby , .

- , , .

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

Answer 2

.

ORDER BY  
        CASE @orderBy
            WHEN 1 THEN CAST(received_date AS SQL_VARIANT)  -- 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

(int, string, date)

DECLARE @P INT = Datepart(SECOND, Getdate())%3;

SELECT 'Sorting By ' + CASE @P
            WHEN 1 THEN 'modify_date'
            WHEN 2 THEN 'object_id'
            ELSE 'name'
          END  

SELECT object_id,
       name,
       modify_date
FROM   sys.objects
ORDER  BY CASE @P
            WHEN 1 THEN CAST(modify_date AS SQL_VARIANT)
            WHEN 2 THEN object_id
            ELSE name
          END  

Answer 3

I think the next option would be cleaner (similar to RichardTheKiwi's approach)

WITH error_table AS  
(  
    SELECT ROW_NUMBER() OVER 
            (
            ORDER BY
                CASE WHEN @orderBy = 1 THEN received_date END,
                CASE WHEN @orderBy = 2 THEN message_id END ,
                CASE WHEN @orderBy = 3 THEN zibmat.short_name END,
                CASE WHEN @orderBy =4 THEN error_action.short_name END, 
                CASE WHEN @orderBy =5 THEN ime.[allocated_date] END 
            ) AS RowNumber

    ,ime.[ijis_message_error_id]  
    ,ime.[message_id] 
    --..other columns...
    -- FROM YOURTABLE
)