Column alias ORDER BY question.

[grjohnson]

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> SELECT SYSDATE "newdate" FROM DUAL;

newdate
---------
12-APR-02

SQL> SELECT SYSDATE "newdate" FROM DUAL
ORDER BY newdate;
ORDER BY newdate
*
ERROR at line 2:
ORA-00904: invalid column name


SQL> SELECT SYSDATE AS newdate FROM DUAL
ORDER BY newdate;

NEWDATE
---------
12-APR-02

So, what the difference between the two different ways in which to alias a column, and why can you order by one statement and not the other?

[oraka6]

I am guessing that Oracle automatically convert any alias to CAP.
As a result, in the first sql statement, when you put double quote over "newdate", the double quote keeps the alia in lower case. While in the ORDER BY clause, NEWDATE is converted to CAP. Thus, there's a missmatch.


1 SELECT SYSDATE "NEWDATE" FROM DUAL
2* ORDER BY newdate
SQL> /

NEWDATE
---------
11-APR-02

SQL> SELECT SYSDATE AS newdate FROM DUAL
2 ORDER BY newdate;

NEWDATE
---------
11-APR-02

SQL> EDIT
Wrote file afiedt.buf

1 SELECT SYSDATE newdate FROM DUAL
2* ORDER BY newdate
SQL> /

NEWDATE
---------
11-APR-02


1 SELECT SYSDATE "newdate" FROM DUAL
2* ORDER BY LOWER('newdate')
SQL> /

newdate
---------
11-APR-02

[grjohnson]

Thanks oraka6, interesting.

Cheers,