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

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Wed, 09 Mar 2005 15:39:48 -0800
Message-ID: <1110411395.291134@yasure>

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_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Wed Mar 09 2005 - 17:39:48 CST