Incompatible data types in case statement error

[cognos74]

hi I need to create dynamic sorting prompt in the prompt page.

I have a facility prompt and need to use one more prompt for sorting

sorting prompt static choice is 1) Name 2) ID Number 3)Date

in the query I have created a sort key data item like this:
case
when ?Sort_Column? = 'Name' then [Name]
when ?Sort_Column? = 'Number' then [Number]
when ?Sort_Column? = 'Date' then [Date]
else
' '
end

when I run I am getting "Incompatible data types in case statement." error.  not sure why I am getting this how to resolve.
Thanks,

[hespora]

Case statements and if...then...else statements need to have the same datatype output in all possible scenarios. In your case, name is likely a string, number is likely integer, and date will be date or timestamp. Cognos cannot consolidate those, and it completely ignores the fact that, per report execution, you can only have one of those outputs (i.e. that, what cognos perceives as a problem, isn't even a problem).

To solve the issue, I'd do this:
- split up the field into three separate fields; one for name, one for idno, and one for date. Each should either be its specific value or null (*not* ''). Sort your output by all three of them, and conditionally hide the two not selected.