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

"Norbert Winkler" <norbert.winkler1 (AT) gmx (DOT) de> a écrit dans le message de news: 18zm0amjxzqek.1iinhm3b7czd4$.dlg (AT) 40tude (DOT) net...
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

Hallo Norbert,
try it with aliases and you see that your first statement fails too.

select * from TST_TAB t WHERE YEAR = (SELECT y.YEAR FROM TST_ONE_YEAR
y);

As Michael said, the year column is coming from tst_tab and this is
true for all rows.

works as designed ... but not as expected ;-)

tschüss
Kay

On Sep 24, 7:17*am, Kay Kanekowski <kay.kanekow... (AT) web (DOT) de> wrote:
Actually this should not be unexpected behavior since the SQL manual
explicitly states this requirement: "If columns in a subquery have the
same name as columns in the containing statement, then you must prefix
any reference to the column of the table from the containing statement
with the table name or alias. To make your statements easier to read,
always qualify the columns in a subquery with the name or alias of the
table, view, or materialized view."

Reference: Oracle® Database SQL Reference 10g Release 2 (10.2) Part
Number B14200-02 section Using Subqueries

HTH -- Mark D Powell --

On 09/24/2010 10:33 AM, Norbert Winkler wrote:
Apart from the semantics, the technical design sucks.

Do NOT name columns in your design after something
that is not stored. Naming a column YEAR (or JAHR in german)
makes people expect something of a date is being stored.

You actually store character strings, not (part of) dates,
hence YEAR_INDICATOR would be more appropriate.
Calling the column YEAR will bite you. In performance (treating it
as date, using implicit conversions, indexes useless), or while
sorting.
--

Regards,

Frank van Bortel

Am 25.09.2010 16:00, schrieb Mark D Powell:
ok, i correct it:

works as designed ... but not as Norbert and i expect it ;-)

regards
Kay