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
Received on Thu Mar 10 2005 - 20:17:09 CST
