Thread: Using column alias in WHERE clause?

Is it possible to reference a column alias in the WHERE clause? 

I'd like to create a query something like this: 

SELECT cust_id,
CASE WHEN TRIM(UPPER(cust_name)) LIKE TRIM(UPPER( 'Joe Jones' )) THEN '2'       --SAME NAME    WHEN TRIM(UPPER(alias))
LIKETRIM(UPPER( '%Joe Jones%' ))   THEN '1'       --EXISTING ALIAS    ELSE '0'      --NEW NAME
 
END AS name_test
FROM customer
WHERE cust_id = 1234
OR name_test > 0
ORDER BY name_test DESC

When I try it, I get: ERROR:  Attribute "name_test" not found

If I remove name_test from the WHERE clause, the query still runs fine with name_test in the ORDER BY. 

Is there a way to reference the alias in the Where clause as well? This is PostgreSQL 7.3.

Thanks,
August

On Fri, 2003-03-07 at 14:17, August Detlefsen wrote:
> Is it possible to reference a column alias in the WHERE clause?
>
> I'd like to create a query something like this:
>
> SELECT cust_id,
> CASE WHEN TRIM(UPPER(cust_name)) LIKE TRIM(UPPER( 'Joe Jones' )) THEN '2'
>      --SAME NAME
>      WHEN TRIM(UPPER(alias)) LIKE TRIM(UPPER( '%Joe Jones%' ))   THEN '1'
>      --EXISTING ALIAS
>      ELSE '0'
>      --NEW NAME
> END AS name_test
> FROM customer
> WHERE cust_id = 1234
> OR name_test > 0
> ORDER BY name_test DESC


select cust_id    , name_test FROM (select cust_id            , CASE .... AS name_test         from customer
wherecust_id = 1234      ) as jnkwhere name_test > 0order by name_test desc; 


> When I try it, I get: ERROR:  Attribute "name_test" not found
>
> If I remove name_test from the WHERE clause, the query still runs fine with name_test in the ORDER BY.
>
> Is there a way to reference the alias in the Where clause as well? This is PostgreSQL 7.3.
>
> Thanks,
> August
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc