X-Original-To: pgsql-sql@postgresql.org
Received: from jester.inquent.com (unknown [216.208.117.7])
	by postgresql.org (Postfix) with ESMTP id 4FAD0476873
	for <pgsql-sql@postgresql.org>; Wed, 12 Mar 2003 16:36:56 -0500 (EST)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by jester.inquent.com (8.12.6/8.12.6) with ESMTP id h2CLauTw057480;
	Wed, 12 Mar 2003 16:36:56 -0500 (EST) (envelope-from rbt@rbt.ca)
Subject: Re: Using column alias in WHERE clause?
From: Rod Taylor <rbt@rbt.ca>
To: August Detlefsen <augustd@energiesonline.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <16609031.1047064636464.JavaMail.root@homer.codemagi.com>
References: <16609031.1047064636464.JavaMail.root@homer.codemagi.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-GNT6BvmByxEjbcNwc9u/"
Organization: 
Message-Id: <1047505015.55840.48.camel@jester>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.2 
Date: 12 Mar 2003 16:36:56 -0500
X-Archive-Number: 200303/314
X-Sequence-Number: 12353

--=-GNT6BvmByxEjbcNwc9u/
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Fri, 2003-03-07 at 14:17, August Detlefsen wrote:
> Is it possible to reference a column alias in the WHERE clause?=20
>=20
> I'd like to create a query something like this:=20
>=20
> SELECT cust_id,
> CASE WHEN TRIM(UPPER(cust_name)) LIKE TRIM(UPPER( 'Joe Jones' )) THEN '2'=
=20=20=20
>      --SAME NAME
>      WHEN TRIM(UPPER(alias)) LIKE TRIM(UPPER( '%Joe Jones%' ))   THEN '1'=
=20=20=20
>      --EXISTING ALIAS
>      ELSE '0'=20=20
>      --NEW NAME
> END AS name_test
> FROM customer
> WHERE cust_id =3D 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 =3D 1234
       ) as jnk
 where name_test > 0
 order by name_test desc;


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

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

--=-GNT6BvmByxEjbcNwc9u/
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+b6h36DETLow6vwwRApDjAJ9cvopF1dZGJVAncusIUUfRNZWeEQCcDbB6
fmWEr7OWiVZpI0uEZO/gMXY=
=E1o3
-----END PGP SIGNATURE-----

--=-GNT6BvmByxEjbcNwc9u/--