Re: Converting from MS Access field aliases

> 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...?

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

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