pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing column alias in WHERE clause?
2+ messages / 2 participants
[nested] [flat]
* Using column alias in WHERE clause?
@ 2003-03-07 19:17 August Detlefsen <augustd@energiesonline.com>
2003-03-12 21:36 ` Re: Using column alias in WHERE clause? Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 2+ messages in thread
From: August Detlefsen @ 2003-03-07 19:17 UTC (permalink / raw)
To: pgsql-sql
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
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Using column alias in WHERE clause?
2003-03-07 19:17 Using column alias in WHERE clause? August Detlefsen <augustd@energiesonline.com>
@ 2003-03-12 21:36 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Rod Taylor @ 2003-03-12 21:36 UTC (permalink / raw)
To: August Detlefsen <augustd@energiesonline.com>; +Cc: 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@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-03-12 21:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-07 19:17 Using column alias in WHERE clause? August Detlefsen <augustd@energiesonline.com>
2003-03-12 21:36 ` Rod Taylor <rbt@rbt.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox