Thread: query problem in 7.2.1: serious planner issue
I cannot sort on a field that I join across tables. Here are the examples: If I do this: SELECT offers.lot_id, lots.project_id FROM offers, lots WHERE offers.lot_id = lots.lot_id AND ... UNION SELECT offers.lot_id, lots_deleted.project_id FROM offers, lots_deleted WHERE offers.lot_id = lots_deleted.lot_id AND ... ORDER BY lot_id Produces the error: Error while executing the query; ERROR: ORDER BY 'lot_id' is ambiguous And if I do this: If I do this: SELECT offers.lot_id, lots.project_id FROM offers, lots WHERE offers.lot_id = lots.lot_id AND ... UNION SELECT offers.lot_id, lots_deleted.project_id FROM offers, lots_deleted WHERE offers.lot_id = lots_deleted.lot_id AND ... ORDER BY lots.lot_id Produces the error: Error while executing the query; ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns Hmm, it does not like the table name reference either, so what if I rename the result column... If I try this: SELECT offers.lot_id, lots.project_id, offer.lot_id AS offers_lot_id FROM offers, lots WHERE offers.lot_id = lots.lot_id AND ... UNION SELECT offers.lot_id, lots_deleted.project_id, offer.lot_id AS offers_lot_id FROM offers, lots_deleted WHERE offers.lot_id = lots_deleted.lot_id AND ... ORDER BY offers_lot_id Produces the error: Error while executing the query; ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns Just for fun I did ths: SELECT offers.lot_id, lots.project_id FROM offers, lots WHERE offers.lot_id = lots.lot_id AND ... UNION SELECT offers.lot_id, lots_deleted.project_id FROM offers, lots_deleted WHERE offers.lot_id = lots_deleted.lot_id AND ... ORDER BY offers_lot_id Which of course does not work but produces the error: Error while executing the query; ERROR: Attribute 'offer_lot_id' not found Which distinguishes it from the previous error. Is there a way to do what I am trying to do??? Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com
terry@greatgulfhomes.com writes: > I cannot sort on a field that I join across tables. Here are the examples: > If I do this: > ORDER BY lot_id > Produces the error: > Error while executing the query; ERROR: ORDER BY 'lot_id' is ambiguous It does? I tried to duplicate this: test72=# create table offers(lot_id int); CREATE test72=# create table lots(lot_id int, project_id int); CREATE test72=# create table lots_deleted(lot_id int, project_id int); CREATE test72=# SELECT offers.lot_id, lots.project_id FROM offers, lots test72-# WHERE offers.lot_id = lots.lot_id test72-# UNION test72-# SELECT offers.lot_id, lots_deleted.project_id FROM offers, lots_deleted test72-# WHERE offers.lot_id = lots_deleted.lot_id test72-# ORDER BY lot_id; lot_id | project_id --------+------------ (0 rows) Your third example (with AS) works fine too once I corrected the typos (offer.lot_id -> offers.lot_id, etc). In general, you can ORDER BY the column name or column number of any output column of the UNION construct. If you want to use a name then you'd better be sure only one output column has that name. This is per SQL92 spec; we don't offer any extensions to sort on non-output columns when we're dealing with a UNION. regards, tom lane
On Thu, 4 Jul 2002 terry@greatgulfhomes.com wrote: > I cannot sort on a field that I join across tables. Here are the examples: > > If I do this: > SELECT offers.lot_id, lots.project_id > FROM offers, lots > WHERE offers.lot_id = lots.lot_id > AND ... > UNION > SELECT offers.lot_id, lots_deleted.project_id > FROM offers, lots_deleted > WHERE offers.lot_id = lots_deleted.lot_id > AND ... > ORDER BY lot_id ORDER BY 1 Regards Herbie -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Herbert Liechti http://www.thinx.ch The content management company. Visit http://www.contentx.ch ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Thu, 4 Jul 2002 terry@greatgulfhomes.com wrote: > I cannot sort on a field that I join across tables. Here are the examples: > > If I do this: > SELECT offers.lot_id, lots.project_id > FROM offers, lots > WHERE offers.lot_id = lots.lot_id > AND ... > UNION > SELECT offers.lot_id, lots_deleted.project_id > FROM offers, lots_deleted > WHERE offers.lot_id = lots_deleted.lot_id > AND ... > ORDER BY lot_id ORDER BY 1 Where the number indicates the position of the column in the resulting table Regards Herbie -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Herbert Liechti http://www.thinx.ch The content management company. Visit http://www.contentx.ch ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~