Message-ID: <56317.62.244.190.66.1184255716.squirrel@www.chuckie.co.uk>
In-Reply-To: <E3D00FA5-C52E-4886-AB45-994162498DA8@joelrichard.com>
References: <E3D00FA5-C52E-4886-AB45-994162498DA8@joelrichard.com>
Date: Thu, 12 Jul 2007 16:55:16 +0100 (BST)
Subject: Re: Converting from MS Access field aliases
From: "Nicholas Barr" <nicky@chuckie.co.uk>
To: "Joel Richard" <postgres@joelrichard.com>
Cc: pgsql-sql@postgresql.org
Reply-To: nicky@chuckie.co.uk
User-Agent: SquirrelMail/1.4.8-1
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal

> Good morning,
>
> Oh joyous day! We are upgrading a legacy database system from MS
> Access to PostgreSQL! Yay!
>
> Ok, rejoicing over. Here's our issue and PLEASE point me to the right
> place if this has been discussed before.
>
> In MS Access one can reuse field aliases later in the same query. For
> example:
>
>    SELECT field1 / 2 AS foo,
>           field2 * 2 AS bar,
>           foo + bar AS total
>    WHERE foo < 12;
>
> The first two fields are fine, it's the third that's a problem. The
> database reports
>
>    ERROR:  column "foo" does not exist
>
> This type of situation is happening -many- times in well over fifty
> existing SELECT..INTO and INSERT INTO queries. The obvious solution
> here is to copy the code around to eliminate the need to reuse "foo"
> and "bar" in the query:
>
>    SELECT field1 / 2 AS foo,
>           field2 * 2 AS bar,
>           (field1 / 2) + (field2 * 2) AS total
>    WHERE (field1 / 2) < 12;
>
> But this is a bit ugly and cumbersome and in our case, not desirable
> since foo and bar get used many times in the remains of the query. To
> replace them with the formulae means that debugging is quite
> difficult and very prone to errors.
>
> Does anyone have suggestions on how to circumvent this in a more
> graceful manner? I mean I could probably find a way to do this with a
> couple of queries and some views, or maybe write a function (or more
> like 30 functions) to do the work, but both of those only add to the
> workload in an undesirable manner. :)
>
> To complicate matters, performance is a concern. We're operating on
> upwards of a billion records. Not all at the same time, but the goal
> is to run these  a series of calculations will be done on all of the
> data.
>
> Thanks for any input that you might have.
>
> --Joel

Are you able to restructure your queries to be something like...?

SELECT
	t2.foo + t2.bar
FROM
	(
		SELECT
			field1 / 2 AS foo,
			field2 * 2 AS bar
		FROM
			table1 t1
		WHERE
			foo < 12
	) AS t2

PG allows sub-clauses and statements in the FROM clause, as well as in the
WHERE & SELECT clauses.

Not sure how these will perform on a billion rows, so a few EXPLAIN
ANALYSE outputs might help tune the queries some more if you have them.
The results should be semantically comparable to the MS Access queries
though (I think).

Nick