ORDER BY issue in Oracle

[WolfShade]

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 solution]

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.

[WolfShade]

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.

V/r,

^_^