SQL> create table andrew
2 (col1 number, col1 varchar2(10))
3 /
(col1 number, col1 varchar2(10))
*
ERROR at line 2:
ORA-00957: duplicate column name
SQL>
So, if you are only selecting from 1 table, Oracle always knows where to get the column(s) from. However, if you join 2 (or more) tables or views, this is no longer true:
SQL> select sid, value
2 from v$mystat, v$sysstat
3 where statistic# = statistic#
4 and name = 'CPU used by this session'
5 /
where statistic# = statistic#
*
ERROR at line 3:
ORA-00918: column ambiguously defined
SQL>
To stop this happening, you need to tell Oracle which table or view to get each column from. You can do this by putting the table or view name before the column name as follows:
SQL> l
1 select sid, v$mystat.value
2 from v$mystat, v$sysstat
3 where v$mystat.statistic# = v$sysstat.statistic#
4* and name = 'CPU used by this session'
SQL> /
SID VALUE
---------- ----------
684 37
SQL>
Alternatively, you can give them aliases like this:
SQL> l
1 select sid, a.value
2 from v$mystat a, v$sysstat b
3 where a.statistic# = b.statistic#
4* and name = 'CPU used by this session'
SQL> /
SID VALUE
---------- ----------
684 41
SQL>
You need to do this for value and statistic# because they appear in both v$mystat and v$sysstat. However, you do not need to do it for sid because it only appears in v$mystat:
SQL> desc v$mystat
Name Null? Type
-------------------------- -------- ------------------
SID NUMBER
STATISTIC# NUMBER
VALUE NUMBER
SQL> desc v$sysstat
Name Null? Type
-------------------------- -------- ------------------
STATISTIC# NUMBER
NAME VARCHAR2(64)
CLASS NUMBER
VALUE NUMBER
STAT_ID NUMBER
SQL>
No comments:
Post a Comment