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_at_yasure>
>
> 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.
>
>
> 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
Date: Wed, 09 Mar 2005 15:39:48 -0800
Message-ID: <1110411395.291134_at_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 Thu Mar 10 2005 - 00:39:48 CET