This discussion is archived
2 Replies Latest reply: Nov 23, 2012 3:12 PM by Hoek RSS

SQL abbreviations don't work in Oracle

829559 Newbie
Currently Being Moderated
I'm surprised that statement #1 works in both MSSQLServer 2012 and Oracle 11g but statement #2 only works in MSSQLServer 2012 and not Oracle 11g. Is not the "as" feature standard SQL?

#1
SELECT name, id FROM owner WHERE NOT EXISTS (SELECT * FROM upfall WHERE owner_id = owner.id)

#2
SELECT name, id FROM owner AS o WHERE NOT EXISTS (SELECT * FROM upfall AS u WHERE owner_id = o.id)
  • 1. Re: SQL abbreviations don't work in Oracle
    rp0428 Guru
    Currently Being Moderated
    >
    I'm surprised that statement #1 works in both MSSQLServer 2012 and Oracle 11g but statement #2 only works in MSSQLServer 2012 and not Oracle 11g. Is not the "as" feature standard SQL?
    >
    In Oracle the AS keyword is used for column aliases, not for table correlation names. This would be the Oracle syntax
    SELECT name, id FROM owner o WHERE NOT EXISTS (SELECT * FROM upfall u WHERE u.owner_id = o.id) 
    The SQL standard uses the AS keyword for column correlation names but it is not used for table correlation names. Also IBM and SYBASE implement the same syntax as Oracle: no AS keyword for table correlation names.
  • 2. Re: SQL abbreviations don't work in Oracle
    Hoek Guru
    Currently Being Moderated
    Did you check the MSSQLServer documentation?
    Or Oracle's Documentation Library @ http://www.oracle.com/pls/db112/homepage?

    One thing you can use AS for is aliasing column names.
    But not table names:
    SQL> select 'bla' as my_col
      2  from  dual;
    
    MY_COL
    ----------
    bla
    
    SQL> select 'bla'
      2  from  dual as my_tab;
    from  dual as my_tab
               *
    ERROR at line 2:
    ORA-00933: SQL command not properly ended
    You can define DB objects AS procedure or function etc.
    Just read the docs and FAQ's first.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points