ORDER BY issue in Oracle

Question

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,^_^

Carl Von Stetten · Accepted Answer

How about adding to your SELECT statement:

lower(lname) AS lname_lcase, lower(fname) AS fname_lcase

then changing your ORDER BY to:

ORDER BY lname_lcase ASC, fname_lcase ASC

I'm not familiar with Oracle's SQL dialect, but the error seems to indicate that you can't sort on the result of an inline function inside the ORDER BY statement.

-Carl V.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded