Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: order by upper(colname)

    I am running some Oracle sql similar to the following:

    select person_id, fname||' '||lname as name
    from person where org_id = 1
    order by upper(name);

    Some names will be Initial Capitalization format and some names will be in all caps. The upper function is included on the name so Oracle will sort these names appropriately.

    But when I do a union:

    select person_id, fname||' '||lname as name
    from person where org_id = 1
    union
    select person_id, fname||' '||lname as name
    from person where org_id = 2
    order by upper(name)

    I get ORA-01785 - ORDER BY item must be the number
    of a SELECT-list expression

    How can I do a union and apply a similar ORDER BY clause without getting this error?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    UPPPER is incorrectly placed on the ORDER clause when it belongs in the SELECT clause
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    The error is telling you all! name is not a column in your select list. You might try:

    Code:
    select * from (
     select person_id, fname||' '||lname as name 
       from person where org_id = 1
     union
     select person_id, fname||' '||lname as name 
       from person where org_id = 2
    )
    order by upper(name)

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Listen to Julian.

    btw - Why have a union??
    PHP Code:
    select from (
     
    select person_idfname||' '||lname as name 
       from person where org_id IN 
    (1,2))
    order by upper(name
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Couldn't you also use:

    select person_id, fname||' '||lname as name
    from person where org_id = 1
    union
    select person_id, fname||' '||lname as name
    from person where org_id = 2
    order by upper(fname||' '||lname)

    -cf

Posting Permissions

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