-
I would like to sort query case insensitive. So I do something like,
SELECT * FROM tab1 ORDER BY UPPER(tab1.a);
It works. But in the following case,
SELECT tab1.a AS a FROM tab1 UNION SELECT tab2.b AS a FROM tab2 ORDER BY UPPER(a);
It doesn't work and got the error ORA-01785: ORDER BY item must be the number of a SELECT-list expression.
Can anybody tell me why it doesn't work, and how to handle the case insensitive sorting when using UNION?
Thanks for any help.
zm
-
Probably the column b from tab2 is not the same type of the column a from tab1. If both were varchar2 for example, it would work. You have to convert at least one of the datatypes because a result is a relation and each column of a relation must have only one datatype. In the example below I converted the two columns to char for avoiding problems:
SELECT A
FROM (SELECT TO_CHAR(tab1.a) as A FROM tab1 UNION
SELECT TO_CHAR(tab2.b) as A FROM tab2)
ORDER BY A;
F.
-
Originally posted by mzhu
SELECT tab1.a AS a FROM tab1 UNION SELECT tab2.b AS a FROM tab2 ORDER BY UPPER(a);
This can't be done, not even in 9i. You simply can't apply a function to an ordering column of an UNION query. In ORDER BY clause you can only use positional notation, or reference a common column name/alias used in both unioned queries, but you cant use function on top of column name or alias. And it has nothing to do with diferent datatypes as Mnemonical suggests.
The only workaround would be to rewrite your query into inline wiew:
SELECT * FROM
(SELECT tab1.a AS a FROM tab1
UNION
SELECT tab2.b AS a FROM tab2)
ORDER BY UPPER(a);
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I'm sorry if I suggested something wrong. I tried to solve his problem using my tables and for a my column was Varchar2 and the other table for b my column was Number, and with this situation I got an error like his - He might have done the same thing as me, or not as well. Whatever, the result will be the same with both scripts.
F.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|