• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

ORDER BY issue in Oracle

LEGEND ,
Feb 02, 2016 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,

^_^

Views

752

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
community guidelines

correct answers 1 Correct answer

Guide , Feb 02, 2016 Feb 02, 2016

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.

Votes

Translate

Translate
Guide ,
Feb 02, 2016 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.

Votes

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
community guidelines
LEGEND ,
Feb 03, 2016 Feb 03, 2016

Copy link to clipboard

Copied

LATEST

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,

^_^

Votes

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
community guidelines
Resources
Documentation