467,114 Members | 1,306 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,114 developers. It's quick & easy.

ORDER BY CASE Problem

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
  • viewed: 3324
Share:
2 Replies
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
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.

Similar topics

10 posts views Thread by Bart Van der Donck | last post: by
13 posts views Thread by Dark Rayden | last post: by
11 posts views Thread by jguilford@cybergroup.com | last post: by
7 posts views Thread by JJ_377@hotmail.com | last post: by
1 post views Thread by Tony Johansson | last post: by
3 posts views Thread by Paul T. Rong | last post: by
104 posts views Thread by Beowulf | last post: by
4 posts views Thread by Pacific Fox | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.