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

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 Rielau
DB2 SQL Compiler Development
IBM Toronto Lab