Re: Subselect in SELECT

DA Morgan wrote:
> Charles Hooper wrote:
> > Quick check:
> > SELECT
> > *
> > FROM
> > V$RESERVED_WORDS
> > WHERE
> > KEYWORD='AMOUNT';
> >
> > (No rows)
>
> On the other hand ... NAME ... is a reserved word
> so I wouldn't bet on "persons.name" if it were me.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Good catch, I overlooked that NAME is a reserved word: SELECT
  *
FROM
  V$RESERVED_WORDS
WHERE
  KEYWORD='NAME';

KEYWORD                            LENGTH R R R R D
------------------------------ ---------- - - - - -
NAME                                    4 N N N N N

Confirming that having a column name of NAME can cause problems: SELECT
  COUNT(*)
FROM
  DBA_TAB_COLS
WHERE
  COLUMN_NAME='NAME'
  AND OWNER='SYS';   COUNT(*)

       608

Must be something wrong?

SELECT
  TABLE_NAME,
  COLUMN_NAME
FROM
  DBA_TAB_COLS
WHERE
  COLUMN_NAME='NAME'
  AND OWNER='SYS'
  AND ROWNUM<=20;

TABLE_NAME                     COLUMN_NAME
------------------------------ -----------
UNDO$                          NAME
TS$                            NAME
OBJ$                           NAME
COL$                           NAME
USER$                          NAME
CON$                           NAME
UGROUP$                        NAME
SYN$                           NAME
KU_NOEXP_TAB                   NAME
AQ$_KUPC$DATAPUMP_QUETAB_S     NAME
AQ$_KUPC$DATAPUMP_QUETAB_H     NAME
AQ$_KUPC$DATAPUMP_QUETAB_G     NAME
AQ$_KUPC$DATAPUMP_QUETAB_I     NAME
AQ$_SCHEDULER$_JOBQTAB_S       NAME
AQ$_SCHEDULER$_JOBQTAB_H       NAME
AQ$_SCHEDULER$_JOBQTAB_G       NAME
AQ$_SCHEDULER$_JOBQTAB_I       NAME
SCHEDULER$_JOB_ARGUMENT        NAME
SCHEDULER$_PROGRAM_ARGUMENT    NAME
SCHEDULER$_EVENT_LOG           NAME

It might be a bit unplesant if Oracle changed the behavior of the NAME reserved word. Anyone want to hack the dictionary and change some of the "N"s shown in the V$RESERVED_WORDS view to "Y" and explain what happens? (don't do this!)

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jan 20 2007 - 20:17:50 CST