Re: Can I use an alias name is a where clause

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Thu, 10 Mar 2005 13:41:00 -0500
Message-ID: <39biksF60n7rnU1_at_individual.net>

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

Received on Thu Mar 10 2005 - 19:41:00 CET