By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

ORDER BY CASE Problem

P: 10
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;
Aug 25 '07 #1
Share this Question
Share on Google+
2 Replies


P: 10
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?
Aug 26 '07 #2

P: 3
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(column names) specified in the outer query
Aug 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.