[SOLVED] Dynamic Query Item Used for Sorting

Dynamic Query Item Used for Sorting

I'm using Cognos Framework Manager and I'm creating a Data Item for a dynamic sort. I'm creating the Data Item using a CASE WHEN, here's my sample code:

CASE #prompt('SortOrder', 'string')#
WHEN 'Date' THEN <Date Column>
WHEN 'ID' THEN <String Column>
END

I'm getting this error QE-DEF-0405 Incompatible data types in case statement. Although I can cast the date column into a string wouldn't that make sort go wrong for the 'date' option? Should I cast the date column in a different way, cast the whole case, or am I barking at the wrong tree? In line with my question, should there be a general rule when creating dynamic columns via CASE with multiple column data types?

Solution

Column in Framework Manager should have datatype. Only one datatype. So you need to cast your date column to correctly sortable string. E.g. 'yyyy-mm-dd' format.