ORDER BY CASE Problem

[frankmusion]

Hi Im trying to do the following but keeps giving me an "The data types of the result-expression are not compatible". What am i doing wrong?

SELECT PART_NAME, ROWNUM
FROM
(SELECT PART_NAME, ROW_NUMBER() OVER(ORDER BY CASE columnName WHEN('PART_ID') THEN PART_ID ELSE PART_NAME END) AS ROWNUM
FROM NULLID.PARTS) PARTS
WHERE ROWNUM BETWEEN 21 AND 30;

[frankmusion]

Ok I got the solution. I hope this helps someone! Each CASE must output the same DATA TYPE. So if you have a CASE with int columns and varchars columns then all you have to do is have 2 CASE Statements and seperate them by comma's.

exp:

ORDER BY CASE WHEN (Predicate) THEN VarcharColumnn END, CASE WHEN (Predicate) THEN IntColumn END

Can anyone confirm if this is the best way to do this if you have to Order by different type columns?

[shalini kudapa]

Hi Im trying to do the following but keeps giving me an "The data types of the result-expression are not compatible". What am i doing wrong?

SELECT PART_NAME, ROWNUM
FROM
(SELECT PART_NAME, ROW_NUMBER() OVER(ORDER BY CASE columnName WHEN('PART_ID') THEN PART_ID ELSE PART_NAME END) AS ROWNUM
FROM NULLID.PARTS) PARTS
WHERE ROWNUM BETWEEN 21 AND 30;

Problem i think is with the data types of the attributes. the values the inner query is returning is not same as the data types of the attributes(colu mn names) specified in the outer query