-
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?
-
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
-
Thanks oraka6, interesting.
Cheers,
