Posted by --CELKO-- on 04/04/07 16:48

>> Is there a way to do this in a single query with 2 different data types? <<

The CASE expression has to have one and only one data type, just like
any other expression in SQL and strongly typed languages.

The trick is to keep both columns and NULL one of the out or make it a
constant, somethign like this:

ROW_NUMBER() OVER (ORDER BY
CASE WHEN @x = 1 THEN decimal_col ELSE CAST(NULL AS DECIMAL(s,p)) END,
CASE WHEN @x = 2 THEN text_col ELSE CAST (NULL AS CHAR(n)) END) AS
sort_order

The casting is a bit redundant, but a nice reminder.

[Back to original message]