Table Alias in Oracle and SQL Server.

Sometimes we assume things to work and not test it, but it surprise you and give you a reason to learn.

We have a product that works on top of both Oracle and SQL Server, hence we are bound to compliant SQL syntax as ANSI or validated on both.
For one of our changes, we had written SQL as below to run on both SQL Server and Oracle.

SELECT COL1 AS COLUMN1 FROM TABLE1 AS TABLE_ALIAS

As it being a simple change,we (Primarily I) only tested on SQL Server and was confident enough that it will work in Oracle.
But is unfold surprise with below Exception in Oracle and works fine in SQL Server.

ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

lets try to understand with Example in Oracle,

select 'Hi Error' AS Hello from DUAL AS d;

We are using AS keyword to define alias for both Column and Table.
In Oracle, “AS” keyword for Alias only works for Column and not for Table. Didn’t find anything on oracle documentation.

Finally had changed to similar SQL as below.
select 'Hi Error' AS Hello from DUAL d;

HELLO
--------
Hi Error

Learning, Test and don’t assume.!

About Deepak Mahto

Having 9+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s