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?
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.
Brilliant, Carl Von Stetten!
I was a bit confused, because the error message made me think that Oracle thought I was using column index numbers instead of column names. But you nailed it. Works absolutely as I wanted it to.