> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly