Re: alias - do i need to use?

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sun, 13 Feb 2005 16:47:20 -0500
Message-ID: <zNOdnb2Mh_9zV5LfRVn-hQ@comcast.com>

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:1108315901.812802.286460_at_o13g2000cwo.googlegroups.com...
> Besides Sybrand's accurately directed comments about actually trying
> the SQL and reading the manuals if you are using the standard DEPT
> table found in many Oracle examples then from memory I believe the
> correct column name is dname not d_name. What you probably meant to
> write was d.dname.
>
> As a matter of good form you should alias the column names in the
> select list whenever you have a join so that whenever another developer
> reads the SQL statement he or she will know where the columns come from
> even without being familar with the tables. As long as all the columns
> names in the select list are unique amoung the tables in the join there
> is no technical requirement to alias the columns in the select list.
> The same is true for the where clause. When the same column name
> appears in two or more tables then you need to identify from which
> table the column is to be accessed from.
>
> HTH -- Mark D Powell --
>

in addition to mark's comments -- even if a column of the same name does not currently occur in both tables, it's not a bad idea to fully alias every column reference. that way, if either table is altered to include a new column with the same name as a column in the other table, the SQL won't break:

SQL> select ename, dname, loc
  2 from emp e, dept d
  3 where e.deptno = d.deptno
  4 /

ENAME DNAME LOC
---------- -------------- -------------

CLARK      ACCOUNTING     NEW YORK
KING       ACCOUNTING     NEW YORK
MILLER     ACCOUNTING     NEW YORK
smith      RESEARCH       DALLAS
ADAMS      RESEARCH       DALLAS
FORD       RESEARCH       DALLAS
SCOTT      RESEARCH       DALLAS
JONES      RESEARCH       DALLAS
ALLEN      SALES          CHICAGO
BLAKE      SALES          CHICAGO
MARTIN     SALES          CHICAGO
JAMES      SALES          CHICAGO
TURNER     SALES          CHICAGO
WARD       SALES          CHICAGO

SQL> alter table emp add loc varchar2(30);

Table altered.

SQL> select ename, dname, loc
  2 from emp e, dept d
  3 where e.deptno = d.deptno
  4 /
select ename, dname, loc

ERROR at line 1:
ORA-00918: column ambiguously defined

++ mcs Received on Sun Feb 13 2005 - 15:47:20 CST