HAVING and column alias

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: HAVING and column alias
Date: 2003-02-21 18:03:46
Message-ID: 3E566A02.1030204@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I have the following query which gets generated dynamically from
a user-interface, particularly the subselect in the target list.
I cannot rewrite the subselect in the target list as a normal
join expression, since additional target list expressions may be
specified by the user. The below is just an example:

SELECT
SUM(p.qty),
(SELECT date_trunc('day', sales.active)
FROM sales
WHERE sales.purchase = p.purchase) AS field1
FROM purchases p
GROUP BY field1;

This works fine, unless the subselect in the target list doesn't
match any rows, in which case NULL is the result of the
subselect. I would like to further qualify the query with a
HAVING clause, but PostgreSQL doesn't like column aliases nor
ordinal values in the HAVING clause:

SELECT
SUM(p.qty),
(SELECT date_trunc('day', sales.active)
FROM sales
WHERE sales.purchase = p.purchase) AS field1
FROM purchases p
GROUP BY field1
HAVING (field1 IS NOT NULL);

ERROR: Attribute 'field1' not found

This is PostgreSQL 7.2.1. Any tips?

Mike Mascari
mascarm(at)mascari(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2003-02-21 18:10:29 transactions
Previous Message Greg Stark 2003-02-21 17:48:04 cluster questions