Re: mysterious behaviour where = (SELECT JAHR ...

"Norbert Winkler" <norbert.winkler1_at_gmx.de> a écrit dans le message de news: 18zm0amjxzqek.1iinhm3b7czd4$.dlg_at_40tude.net...
| Hi,
|
| the following script creates two tables. The problem causes due to an error
| using a not existing YEAR column.
| My error, in the real tables, dues to a column named "JAHR" the german word
| for year. For testing in a non-german-environment I've replaced JAHR with
| YEAR.
|
|
| In Statement <1> all records are shown.
| The machine should show an error like it does with statement <2> or show
| only the record with "2010" like in the correct statment <3>.
|
| __________________________________________________
| CREATE TABLE TST_ONE_YEAR (
| TST_YEAR VARCHAR2(4) NOT NULL
| )
| /
| CREATE TABLE TST_TAB (
| YEAR VARCHAR2(4) NOT NULL
| )
| /
|
| INSERT INTO TST_ONE_YEAR (TST_YEAR) VALUES ('2010');
|
| INSERT INTO TST_TAB (YEAR) VALUES ('2008');
| INSERT INTO TST_TAB (YEAR) VALUES ('2009');
| INSERT INTO TST_TAB (YEAR) VALUES ('2010');
| INSERT INTO TST_TAB (YEAR) VALUES ('XXXX');
|
| commit;
|
| select * from TST_ONE_YEAR;
| select * from TST_TAB;
|
| /* <1> */
| select * from TST_TAB WHERE YEAR = (SELECT YEAR FROM TST_ONE_YEAR);
| /* <2> */
| select * from TST_TAB WHERE YEAR = (SELECT YEAR1 FROM TST_ONE_YEAR);
| /* <3> */
| select * from TST_TAB WHERE YEAR = (SELECT TST_YEAR FROM TST_ONE_YEAR);
| ________________________________________________
|
|
|
| --
| Norbert
| Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
| Production

In <1> The YEAR in the subquery refers to the YEAR column of TST_TAB. As it does not exist in TST_ONE_YEAR, Oracle (and any RDBMS) has a look to tables one level outer.

Regards
Michel Received on Fri Sep 24 2010 - 03:39:00 CDT