Message-ID: <40023E79.10705@klaster.net>
Date: Mon, 12 Jan 2004 07:28:09 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
	rv:1.6b) Gecko/20031208
MIME-Version: 1.0
To: Robert Creager <Robert_Creager@LogicalChaos.org>
Cc: PGSQL <pgsql-sql@postgresql.org>
Subject: Re: Problem with NOT IN portion of query.
References: <20040111210454.4ee900ae.Robert_Creager@LogicalChaos.org>
In-Reply-To: <20040111210454.4ee900ae.Robert_Creager@LogicalChaos.org>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

Dnia 2004-01-12 05:04, Użytkownik Robert Creager napisał:
> Hey All,
> 
> Probably doing something stupid, and I'm too tired to see what.  The query I'm
> trying to execute is:
> 
> SELECT date_trunc( 'hour', "when" )::timestamp AS
> period FROM readings WHERE period NOT IN (SELECT "time" FROM
> hour.summary_period) GROUP BY period ORDER BY period;
> 
> Where the table definitions are:
> 
> CREATE TABLE readings ( "when" TIMESTAMP DEFAULT now() NOT NULL PRIMARY KEY );
> CREATE SCHEMA hour;
> CREATE TABLE hour.summary_period ( "time" TIMESTAMP NOT NULL );
> 
> The error is:
> 
> ERROR:  column "period" does not exist
> 
> When I remove the NOT IN (and associated WHERE), the query works fine.
Your problem has nothing to "NOT IN".
Your query works fine, when you remove column alias from WHERE clause - 
it's beacause WHERE clause is executed *before* data output (and before 
column aliases). You can still use column aliases in "GROUP BY" and 
"ORDER BY".

Regards,
Tomasz Myrta