From: | Rod Taylor <rbt(at)rbt(dot)ca> |
---|---|
To: | August Detlefsen <augustd(at)energiesonline(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Using column alias in WHERE clause? |
Date: | 2003-03-12 21:36:56 |
Message-ID: | 1047505015.55840.48.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
where cust_id = 1234
) as jnk
where 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Rod Taylor <rbt(at)rbt(dot)ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
From | Date | Subject | |
---|---|---|---|
Next Message | Rod Taylor | 2003-03-12 21:43:09 | Re: "hide" values in a column |
Previous Message | Ryan | 2003-03-12 21:32:32 | nearest match |