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

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Wed, 09 Mar 2005 18:49:13 -0500
Message-ID: <399gaqF5tadvvU1@individual.net>

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

Received on Wed Mar 09 2005 - 17:49:13 CST