2 Replies Latest reply: Nov 23, 2012 5:12 PM by Hoek RSS

    SQL abbreviations don't work in Oracle

    user8816970
      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
          >
          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
            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.