X-Original-To: pgsql-sql@postgresql.org
Received: from homer.codemagi.com (unknown [209.209.44.14])
	by postgresql.org (Postfix) with ESMTP id 8AEB54760CC
	for <pgsql-sql@postgresql.org>; Fri,  7 Mar 2003 14:22:23 -0500 (EST)
Received: from homer.codemagi.com (homer [127.0.0.1])
	by homer.codemagi.com (8.12.8/8.12.5) with ESMTP id h27JHGmY031318
	for <pgsql-sql@postgresql.org>; Fri, 7 Mar 2003 11:17:16 -0800
Date: Fri, 7 Mar 2003 11:17:16 -0800
Message-ID: <16609031.1047064636464.JavaMail.root@homer.codemagi.com>
From: August Detlefsen <augustd@energiesonline.com>
Reply-To: August Detlefsen <augustd@energiesonline.com>
To: pgsql-sql@postgresql.org
Subject: Using column alias in WHERE clause? 
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: jwma
X-Archive-Number: 200303/289
X-Sequence-Number: 12329

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