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_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Thu Mar 10 2005 - 16:18:21 CST
