From: August Detlefsen <augustd@energiesonline.com>
To: pgsql-sql@postgresql.org
Subject: Using column alias in WHERE clause?
Date: Fri, 7 Mar 2003 11:17:16 -0800
Message-ID: <16609031.1047064636464.JavaMail.root@homer.codemagi.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: augustd@energiesonline.com
Subject: Re: Using column alias in WHERE clause?
In-Reply-To: <16609031.1047064636464.JavaMail.root@homer.codemagi.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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