Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    3

    Unanswered: Case insensitive Sorting in query containing UNION

    Hello,

    I have to sort the result of a Union query on a column irrespective of the case of the column.



    Ex: select ename from employee

    union

    select ename from dept

    order by upper(ename)



    But I am getting the error: “Order-by items must appear in the select-list if the statement contains set operators.”



    If the query is not using set operators, it is not a problem.

    Ex: select ename from employee

    order by upper(ename)

  2. #2
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    That's in interesting question:

    Two possibilities:


    1. Create a view of the union, then issue the query against the view

    2. Use derived tables (very similar to doing the view solution without having to create an "object"):

    select a.ename from
    (select ename from emp
    union
    select ename from dept
    ) a
    order by upper(a.ename)

  3. #3
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    The derived table solution is pretty neat :-)

    Michael

  4. #4
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    I neglected to mention the fact that you could simply include the "upper(ename)" column in the select list as the error message suggests:

    select ename, upper(ename)
    from emp
    union
    select ename,upper(ename)
    from dept
    order by upper(ename)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •