Highlighted

ORDER BY issue in Oracle

LEGEND ,
Feb 02, 2016

Copy link to clipboard

Copied

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,

^_^

Most Valuable Participant
Correct answer by Carl_Von_Stetten | Most Valuable Participant

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.

Views

484

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

ORDER BY issue in Oracle

LEGEND ,
Feb 02, 2016

Copy link to clipboard

Copied

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,

^_^

Most Valuable Participant
Correct answer by Carl_Von_Stetten | Most Valuable Participant

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.

Views

485

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 02, 2016 0
Most Valuable Participant ,
Feb 02, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2016 0
LEGEND ,
Feb 03, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 03, 2016 0