DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Problem for case insensitive sorting

  1. #1
    Join Date
    Mar 2001
    Posts
    109
    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

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


    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.






  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


    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
  •  


Click Here to Expand Forum to Full Width