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?

       

      V/r,

       

      ^_^