2 Replies Latest reply on Feb 3, 2016 6:16 AM by WolfShade

    ORDER BY issue in Oracle

    WolfShade Level 4

      Hello, all,


      I have a query that includes two UNIONS and many columns.


      At the end, if I use ORDER BY lname ASC, fname ASC it works great.


      If, however, I use ORDER BY lower(lname) ASC, lower(fname) ASC it breaks.


      I tried ORDER BY nlssort(lname) ASC, nlssort(fname) ASC and that breaks, too.


      These are Last Name and First Name columns.  English.  The only non-letter character that should be in either column is an apostrophe (for names like O'Brien, etc.)  Even then, the nlssort should work.


      But in all cases where it breaks, I get an "ORA-01785: ORDER BY item must be the number of a SELECT-list expression" error message.


      The reason I'm trying to use lower() or nlssort() is because some of the names were not entered proper-case, and I want to sort alphabetically, not alphabetically upper then alphabetically lower (as seems to be Oracle default.)


      Default:     Andy                    Needed:  Andy
                   Betty                            Betty
                   David                            charlie
                   charlie                          David
                   donald                           donald


      How can I get the sort that I want, if lower() and nlssort() aren't working?