DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Column alias ORDER BY question.

  1. #1
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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?
    OCP 8i, 9i DBA
    Brisbane Australia

  2. #2
    Join Date
    Mar 2001
    Posts
    77
    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

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Thanks oraka6, interesting.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width