Can I use an alias name is a where clause

[Lig]

The following select statement yeilds an error message ORA-00904:
"Total": invalid identifier.

select t.a + t.b + t.c Total
from some_table t
where Total > 0

Why can't the alias Total be used in the Where clause?

Is there any workaround other than doing something horrible like below

select *
from (select t.a + t.b + t.c Total
from some_table t)
where Total > 0



Cheers
Lig

[Sybrand Bakker]

On Wed, 09 Mar 2005 23:02:47 +0000, Lig <lignite@iol.ie> wrote:
The following select statement yeilds an error message ORA-00904:"Total": invalid identifier. select t.a + t.b + t.c Total from some_table t where Total > 0Why can't the alias Total be used in the Where clause?

That's just how it works.Is there any workaround other than doing something horrible like below select * from (select t.a + t.b + t.c Total from some_table t) where Total > 0

Why do that? You can cut and paste, can you?CheersLig

Please do not cross post to all groups you can spell. All groups are
monitored and answered by the same small group of people. You're
wasting both bandwith and people's time.


--
Sybrand Bakker, Senior Oracle DBA

[Serge Rielau]

Lig wrote: The following select statement yeilds an error message ORA-00904: "Total": invalid identifier. select t.a + t.b + t.c Total from some_table t where Total > 0 Why can't the alias Total be used in the Where clause?
The values in the select list do not "exist" before the where clause is
executed.
The order of execution semantically is:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Think about this:
SELECT x/y as z FROM T WHERE y IS NOT NULL AND z = 5

If z is computed before y IS NOT NULL the query will be in trouble.
Is there any workaround other than doing something horrible like below select * from (select t.a + t.b + t.c Total from some_table t) where Total > 0
That's how how set processing works.
You will need to _accept_ it if you want to survive with SQL.
You will need to learn to _appreciate_ it if you want to become good
with SQL. :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Daniel Morgan]

Lig wrote:
The following select statement yeilds an error message ORA-00904: "Total": invalid identifier. select t.a + t.b + t.c Total from some_table t where Total > 0 Why can't the alias Total be used in the Where clause? Is there any workaround other than doing something horrible like below select * from (select t.a + t.b + t.c Total from some_table t) where Total > 0 Cheers Lig

What's your problem?

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Mar 9 15:36:41 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table some_table (
2 a NUMBER(1),
3 b NUMBER(1),
4 c NUMBER(1));

Table created.

SQL> insert into some_table values (1,1,1);

1 row created.

SQL> insert into some_table values (0,0,0);

1 row created.

SQL> commit;

Commit complete.

SQL> select *
2 from (select t.a + t.b + t.c Total
3 from some_table t)
4 where Total > 0;

TOTAL
----------
3

SQL>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Daniel Morgan]

Serge Rielau wrote:
Lig wrote: The following select statement yeilds an error message ORA-00904: "Total": invalid identifier. select t.a + t.b + t.c Total from some_table t where Total > 0 Why can't the alias Total be used in the Where clause? The values in the select list do not "exist" before the where clause is executed. The order of execution semantically is: FROM WHERE GROUP BY HAVING SELECT ORDER BY Think about this: SELECT x/y as z FROM T WHERE y IS NOT NULL AND z = 5 If z is computed before y IS NOT NULL the query will be in trouble. Is there any workaround other than doing something horrible like below select * from (select t.a + t.b + t.c Total from some_table t) where Total > 0 That's how how set processing works. You will need to _accept_ it if you want to survive with SQL. You will need to learn to _appreciate_ it if you want to become good with SQL. :-) Cheers Serge

Serge ... please get yourself a copy of Oracle. Your answer is incorrect
because you assume, incorrectly, the syntax is invalid. The SQL
statement works just fine and the OP's problem is not syntactic.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Serge Rielau]

DA Morgan wrote: Lig wrote: The following select statement yeilds an error message ORA-00904: "Total": invalid identifier. select t.a + t.b + t.c Total from some_table t where Total > 0 Why can't the alias Total be used in the Where clause? Is there any workaround other than doing something horrible like below select * from (select t.a + t.b + t.c Total from some_table t) where Total > 0 Cheers Lig What's your problem? SQL*Plus: Release 10.1.0.3.0 - Production on Wed Mar 9 15:36:41 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> create table some_table ( 2 a NUMBER(1), 3 b NUMBER(1), 4 c NUMBER(1)); Table created. SQL> insert into some_table values (1,1,1); 1 row created. SQL> insert into some_table values (0,0,0); 1 row created. SQL> commit; Commit complete. SQL> select * 2 from (select t.a + t.b + t.c Total 3 from some_table t) 4 where Total > 0; TOTAL ---------- 3 SQL>
combining two answers: Serge ... please get yourself a copy of Oracle. Your answer is incorrect because you assume, incorrectly, the syntax is invalid. The SQL statement works just fine and the OP's problem is not syntactic.

careful with the irony.
Please read the OPs post again. He knows that this second statement
works (he just doesn't like it).

Until I get Oracle cleared with legal, try his first statement (his REAL
issue):

select t.a + t.b + t.c Total
from some_table t where Total > 0

Then, teacher, tell us whether it works, and if not why not.
If you still are convinced that my answer is incorrect, please educate
me on why.

Tom Kyte shall be our judge whether the you, the Oracle expert, is
correct or I, the SQL theorist ;-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark C. Stock]

"Lig" <lignite@iol.ie> wrote in message
news:pgLXd.49194$Z14.37780@news.indigo.ie... The following select statement yeilds an error message ORA-00904: "Total": invalid identifier. select t.a + t.b + t.c Total from some_table t where Total > 0 Why can't the alias Total be used in the Where clause? Is there any workaround other than doing something horrible like below select * from (select t.a + t.b + t.c Total from some_table t) where Total > 0 Cheers Lig

it's not all that horrible...

consider the following:

SQL> create index fbi_ttl_com on emp(sal+nvl(comm,0));

Index created.

SQL> set autotrace on

SQL> select ename, ttl_comp
2 from
3 (
4 select ename, sal+nvl(comm,0) as ttl_comp
5 from emp
6 )
7 where ttl_comp = 800;

ENAME TTL_COMP
---------- ----------
smith 800


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=12)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car
d=1 Bytes=12)

2 1 INDEX (RANGE SCAN) OF 'FBI_TTL_COM' (INDEX) (Cost=1 Card
=1)

++ mcs

[Daniel Morgan]

Serge Rielau wrote: DA Morgan wrote: Lig wrote: The following select statement yeilds an error message ORA-00904: "Total": invalid identifier. select t.a + t.b + t.c Total from some_table t where Total > 0 Why can't the alias Total be used in the Where clause? Is there any workaround other than doing something horrible like below select * from (select t.a + t.b + t.c Total from some_table t) where Total > 0 Cheers Lig What's your problem? SQL*Plus: Release 10.1.0.3.0 - Production on Wed Mar 9 15:36:41 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> create table some_table ( 2 a NUMBER(1), 3 b NUMBER(1), 4 c NUMBER(1)); Table created. SQL> insert into some_table values (1,1,1); 1 row created. SQL> insert into some_table values (0,0,0); 1 row created. SQL> commit; Commit complete. SQL> select * 2 from (select t.a + t.b + t.c Total 3 from some_table t) 4 where Total > 0; TOTAL ---------- 3 SQL> combining two answers: Serge ... please get yourself a copy of Oracle. Your answer is incorrect because you assume, incorrectly, the syntax is invalid. The SQL statement works just fine and the OP's problem is not syntactic. careful with the irony. Please read the OPs post again. He knows that this second statement works (he just doesn't like it). Until I get Oracle cleared with legal, try his first statement (his REAL issue): select t.a + t.b + t.c Total from some_table t where Total > 0 Then, teacher, tell us whether it works, and if not why not. If you still are convinced that my answer is incorrect, please educate me on why. Tom Kyte shall be our judge whether the you, the Oracle expert, is correct or I, the SQL theorist ;-) Cheers Serge

SQL*Plus: Release 10.1.0.3.0 - Production on Thu Mar 10 08:31:28 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select t.a + t.b + t.c Total
2 from some_table t where Total > 0;
from some_table t where Total > 0
*
ERROR at line 2:
ORA-00904: "TOTAL": invalid identifier

How can legal possibly prevent you from having, on your own personal
machine in your house, a copy of a product ... well other than fear
that it is superior to their own offering. ;-)
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Serge Rielau]

DA Morgan wrote: Serge Rielau wrote: DA Morgan wrote: Lig wrote:> The following select statement yeilds an error message ORA-00904:> "Total": invalid identifier.>> select t.a + t.b + t.c Total> from some_table t> where Total > 0>> Why can't the alias Total be used in the Where clause?>> Is there any workaround other than doing something horrible like below>> select *> from (select t.a + t.b + t.c Total> from some_table t)> where Total > 0>>>> Cheers> Lig What's your problem? SQL*Plus: Release 10.1.0.3.0 - Production on Wed Mar 9 15:36:41 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> create table some_table ( 2 a NUMBER(1), 3 b NUMBER(1), 4 c NUMBER(1)); Table created. SQL> insert into some_table values (1,1,1); 1 row created. SQL> insert into some_table values (0,0,0); 1 row created. SQL> commit; Commit complete. SQL> select * 2 from (select t.a + t.b + t.c Total 3 from some_table t) 4 where Total > 0; TOTAL ---------- 3 SQL> combining two answers: Serge ... please get yourself a copy of Oracle. Your answer is incorrect because you assume, incorrectly, the syntax is invalid. The SQL statement works just fine and the OP's problem is not syntactic. careful with the irony. Please read the OPs post again. He knows that this second statement works (he just doesn't like it). Until I get Oracle cleared with legal, try his first statement (his REAL issue): select t.a + t.b + t.c Total from some_table t where Total > 0 Then, teacher, tell us whether it works, and if not why not. If you still are convinced that my answer is incorrect, please educate me on why. Tom Kyte shall be our judge whether the you, the Oracle expert, is correct or I, the SQL theorist ;-) Cheers Serge SQL*Plus: Release 10.1.0.3.0 - Production on Thu Mar 10 08:31:28 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select t.a + t.b + t.c Total 2 from some_table t where Total > 0; from some_table t where Total > 0 * ERROR at line 2: ORA-00904: "TOTAL": invalid identifier
Very good. Can you now explain why Oracle - in full compliance with the
SQL standard - raises the error?

My answer is on record.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Daniel Morgan]

Serge Rielau wrote: DA Morgan wrote: Serge Rielau wrote: DA Morgan wrote:> Lig wrote:>>> The following select statement yeilds an error message ORA-00904:>> "Total": invalid identifier.>>>> select t.a + t.b + t.c Total>> from some_table t>> where Total > 0>>>> Why can't the alias Total be used in the Where clause?>>>> Is there any workaround other than doing something horrible like below>>>> select *>> from (select t.a + t.b + t.c Total>> from some_table t)>> where Total > 0>>>>>>>> Cheers>> Lig>>>>>> What's your problem?>> SQL*Plus: Release 10.1.0.3.0 - Production on Wed Mar 9 15:36:41 2005>> Copyright (c) 1982, 2004, Oracle. All rights reserved.>>> Connected to:> Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production> With the Partitioning, OLAP and Data Mining options>> SQL> create table some_table (> 2 a NUMBER(1),> 3 b NUMBER(1),> 4 c NUMBER(1));>> Table created.>> SQL> insert into some_table values (1,1,1);>> 1 row created.>> SQL> insert into some_table values (0,0,0);>> 1 row created.>> SQL> commit;>> Commit complete.>> SQL> select *> 2 from (select t.a + t.b + t.c Total> 3 from some_table t)> 4 where Total > 0;>> TOTAL> ----------> 3>> SQL> combining two answers: > Serge ... please get yourself a copy of Oracle. Your answer is incorrect > because you assume, incorrectly, the syntax is invalid. The SQL > statement works just fine and the OP's problem is not syntactic. careful with the irony. Please read the OPs post again. He knows that this second statement works (he just doesn't like it). Until I get Oracle cleared with legal, try his first statement (his REAL issue): select t.a + t.b + t.c Total from some_table t where Total > 0 Then, teacher, tell us whether it works, and if not why not. If you still are convinced that my answer is incorrect, please educate me on why. Tom Kyte shall be our judge whether the you, the Oracle expert, is correct or I, the SQL theorist ;-) Cheers Serge SQL*Plus: Release 10.1.0.3.0 - Production on Thu Mar 10 08:31:28 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select t.a + t.b + t.c Total 2 from some_table t where Total > 0; from some_table t where Total > 0 * ERROR at line 2: ORA-00904: "TOTAL": invalid identifier Very good. Can you now explain why Oracle - in full compliance with the SQL standard - raises the error? My answer is on record. Cheers Serge

No ... that question would better be directed to Mr. Townsend as (A) I
do not, can not, and will not speak for Oracle Corp. and (B) I really
couldn't care less as I think the standard is worthless.

From where I sit here in my ivory tower at the university ... I dare any
vendor of any RDBMS to claim they are "in full compliance with the SQL
standard." Want to make that claim about DB2 ... go for it. And remember
that is not "partial" compliance ... that is "full" compliance: Your
words.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Serge Rielau]

DA Morgan wrote: Serge Rielau wrote: DA Morgan wrote: Serge Rielau wrote:> DA Morgan wrote:>>> Lig wrote:>>>>> The following select statement yeilds an error message ORA-00904:>>> "Total": invalid identifier.>>>>>> select t.a + t.b + t.c Total>>> from some_table t>>> where Total > 0>>>>>> Why can't the alias Total be used in the Where clause?>>>>>> Is there any workaround other than doing something horrible like>>> below>>>>>> select *>>> from (select t.a + t.b + t.c Total>>> from some_table t)>>> where Total > 0>>>>>>>>>>>> Cheers>>> Lig>>>>>>>>>>>>>> What's your problem?>>>> SQL*Plus: Release 10.1.0.3.0 - Production on Wed Mar 9 15:36:41 2005>>>> Copyright (c) 1982, 2004, Oracle. All rights reserved.>>>>>> Connected to:>> Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production>> With the Partitioning, OLAP and Data Mining options>>>> SQL> create table some_table (>> 2 a NUMBER(1),>> 3 b NUMBER(1),>> 4 c NUMBER(1));>>>> Table created.>>>> SQL> insert into some_table values (1,1,1);>>>> 1 row created.>>>> SQL> insert into some_table values (0,0,0);>>>> 1 row created.>>>> SQL> commit;>>>> Commit complete.>>>> SQL> select *>> 2 from (select t.a + t.b + t.c Total>> 3 from some_table t)>> 4 where Total > 0;>>>> TOTAL>> ---------->> 3>>>> SQL>>>>>> combining two answers:> > Serge ... please get yourself a copy of Oracle. Your answer is> incorrect> > because you assume, incorrectly, the syntax is invalid. The SQL> > statement works just fine and the OP's problem is not syntactic.>> careful with the irony.> Please read the OPs post again. He knows that this second statement> works (he just doesn't like it).>> Until I get Oracle cleared with legal, try his first statement (his> REAL issue):>> select t.a + t.b + t.c Total> from some_table t where Total > 0>> Then, teacher, tell us whether it works, and if not why not.> If you still are convinced that my answer is incorrect, please> educate me on why.>> Tom Kyte shall be our judge whether the you, the Oracle expert, is> correct or I, the SQL theorist ;-)>> Cheers> Serge SQL*Plus: Release 10.1.0.3.0 - Production on Thu Mar 10 08:31:28 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select t.a + t.b + t.c Total 2 from some_table t where Total > 0; from some_table t where Total > 0 * ERROR at line 2: ORA-00904: "TOTAL": invalid identifier Very good. Can you now explain why Oracle - in full compliance with the SQL standard - raises the error? My answer is on record. Cheers Serge No ... that question would better be directed to Mr. Townsend as (A) I do not, can not, and will not speak for Oracle Corp. and (B) I really couldn't care less as I think the standard is worthless.
You need Mark to explain why the statement fails? Yet you had little
symphathy for the original poster who asked why.... seems like he is a
more inquisitive nature than you are, what's wrong with that?

If Mark doesn't asnwer, meybe Don Deutsch, Fred Zemke or Jim Melton (all
Oracle) will oblige.
From where I sit here in my ivory tower at the university ...
Perhaps you should descend, walk across the courtyard to the tower of
the database faculty (the real one that is) and ask: Why did this
statement fail? Why can the IBM dude, in his arrogance, dare answer the
Oracle question?
BTW, I wouldn't rub that University stuff in.. I also taught at
University: CPR ;-) There is a difference between teaching, and teaching
a CS database class.
I dare any vendor of any RDBMS to claim they are "in full compliance with the SQL standard." Want to make that claim about DB2 ... go for it. And remember that is not "partial" compliance ... that is "full" compliance: Your words.
Shoosh.. again you did not read.
I noted that Oracle is raising this error in full compliance - and so
will IDS, XPS, Teradata, and *crossmyfingersknockonwood* even Sybase and
SQL Server.

No ifs,
No buts,
No DB2 being better message hidden between the lines, ...
Just acknowledging the basic laws of SQL at work.

Simply accept that I do post useful messages, stick to your guns as I
stick to mine and we can happily co-exist.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Townsend]

Serge Rielau wrote:
No ... that question would better be directed to Mr. Townsend as (A) I do not, can not, and will not speak for Oracle Corp. and (B) I really couldn't care less as I think the standard is worthless. If Mark doesn't asnwer, meybe Don Deutsch, Fred Zemke or Jim Melton (all Oracle) will oblige.

Hmm - I've now lost track of the question I'm supposed to be answering.
But in fact I don't think there is a question here at all.
And if there is, then I would refer to Sybrand's initial answer "That's
just how it works". With an explanation as per Serge's post.

[Daniel Morgan]

Serge Rielau wrote: DA Morgan wrote: Serge Rielau wrote: DA Morgan wrote:> Serge Rielau wrote:>>> DA Morgan wrote:>>>>> Lig wrote:>>>>>>> The following select statement yeilds an error message ORA-00904:>>>> "Total": invalid identifier.>>>>>>>> select t.a + t.b + t.c Total>>>> from some_table t>>>> where Total > 0>>>>>>>> Why can't the alias Total be used in the Where clause?>>>>>>>> Is there any workaround other than doing something horrible like>>>> below>>>>>>>> select *>>>> from (select t.a + t.b + t.c Total>>>> from some_table t)>>>> where Total > 0>>>>>>>>>>>>>>>> Cheers>>>> Lig>>>>>>>>>>>>>>>>>>>>>>>> What's your problem?>>>>>> SQL*Plus: Release 10.1.0.3.0 - Production on Wed Mar 9 15:36:41 2005>>>>>> Copyright (c) 1982, 2004, Oracle. All rights reserved.>>>>>>>>> Connected to:>>> Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 ->>> Production>>> With the Partitioning, OLAP and Data Mining options>>>>>> SQL> create table some_table (>>> 2 a NUMBER(1),>>> 3 b NUMBER(1),>>> 4 c NUMBER(1));>>>>>> Table created.>>>>>> SQL> insert into some_table values (1,1,1);>>>>>> 1 row created.>>>>>> SQL> insert into some_table values (0,0,0);>>>>>> 1 row created.>>>>>> SQL> commit;>>>>>> Commit complete.>>>>>> SQL> select *>>> 2 from (select t.a + t.b + t.c Total>>> 3 from some_table t)>>> 4 where Total > 0;>>>>>> TOTAL>>> ---------->>> 3>>>>>> SQL>>>>>>>>>>>>> combining two answers:>> > Serge ... please get yourself a copy of Oracle. Your answer is>> incorrect>> > because you assume, incorrectly, the syntax is invalid. The SQL>> > statement works just fine and the OP's problem is not syntactic.>>>> careful with the irony.>> Please read the OPs post again. He knows that this second statement>> works (he just doesn't like it).>>>> Until I get Oracle cleared with legal, try his first statement (his>> REAL issue):>>>> select t.a + t.b + t.c Total>> from some_table t where Total > 0>>>> Then, teacher, tell us whether it works, and if not why not.>> If you still are convinced that my answer is incorrect, please>> educate me on why.>>>> Tom Kyte shall be our judge whether the you, the Oracle expert, is>> correct or I, the SQL theorist ;-)>>>> Cheers>> Serge>>>>>> SQL*Plus: Release 10.1.0.3.0 - Production on Thu Mar 10 08:31:28 2005>> Copyright (c) 1982, 2004, Oracle. All rights reserved.>>> Connected to:> Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production> With the Partitioning, OLAP and Data Mining options>> SQL> select t.a + t.b + t.c Total> 2 from some_table t where Total > 0;> from some_table t where Total > 0> *> ERROR at line 2:> ORA-00904: "TOTAL": invalid identifier Very good. Can you now explain why Oracle - in full compliance with the SQL standard - raises the error? My answer is on record. Cheers Serge No ... that question would better be directed to Mr. Townsend as (A) I do not, can not, and will not speak for Oracle Corp. and (B) I really couldn't care less as I think the standard is worthless. You need Mark to explain why the statement fails? Yet you had little symphathy for the original poster who asked why.... seems like he is a more inquisitive nature than you are, what's wrong with that? If Mark doesn't asnwer, meybe Don Deutsch, Fred Zemke or Jim Melton (all Oracle) will oblige. From where I sit here in my ivory tower at the university ... Perhaps you should descend, walk across the courtyard to the tower of the database faculty (the real one that is) and ask: Why did this statement fail? Why can the IBM dude, in his arrogance, dare answer the Oracle question? BTW, I wouldn't rub that University stuff in.. I also taught at University: CPR ;-) There is a difference between teaching, and teaching a CS database class. I dare any vendor of any RDBMS to claim they are "in full compliance with the SQL standard." Want to make that claim about DB2 ... go for it. And remember that is not "partial" compliance ... that is "full" compliance: Your words. Shoosh.. again you did not read. I noted that Oracle is raising this error in full compliance - and so will IDS, XPS, Teradata, and *crossmyfingersknockonwood* even Sybase and SQL Server. No ifs, No buts, No DB2 being better message hidden between the lines, ... Just acknowledging the basic laws of SQL at work. Simply accept that I do post useful messages, stick to your guns as I stick to mine and we can happily co-exist. Cheers Serge

Reading <> comprehension.

I assumed your statement, quoted below, was sarcastic.
Very good. Can you now explain why Oracle - in full compliance with the SQL standard - raises the error?

If the error is in full compliance the question seems without value. So
even now, with a full reread, your statement makes no sense. I'll see
if another glass of scotch helps.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)