Received: from presence1.observatoiredesmarques.fr (presence1.observatoiredesmarques.fr [212.73.199.68]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8H8ac792724 for ; Mon, 17 Sep 2001 04:36:38 -0400 (EDT) (envelope-from richard@presenceweb.com) Received: from presenceweb.com (gatei.observatoiredesmarques.fr [213.56.32.22]) by presence1.observatoiredesmarques.fr (8.9.1/_ODM_070798) with ESMTP id IAA20619; Mon, 17 Sep 2001 08:37:31 GMT Message-ID: <3BA5B409.B57A88D2@presenceweb.com> Date: Mon, 17 Sep 2001 10:27:53 +0200 From: Richard NAGY Organization: Presenceweb X-Mailer: Mozilla 4.72 [fr] (X11; U; Linux 2.2.14-6.1.1smp i686) X-Accept-Language: fr, en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@postgresql.org Subject: Re: SQL request change when upgrade from 7.0.2 to 7.1.3 References: <3BA1D7DD.569B218C@presenceweb.com> <18385.1000667491@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------788CA54A1D49006E171C7FE2" X-Archive-Number: 200109/100 X-Sequence-Number: 4764 --------------788CA54A1D49006E171C7FE2 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Tom Lane a écrit : > Richard NAGY writes: > > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, > > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > > aes.sect_id <> 9 > > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 > > UNION > > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, > > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > > aes.sect_id <> 9 > > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and > > aes.ent_id > > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and > > sect_id <> 3 > > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, > > e.nom > > ORDER BY applied to the result of a UNION has to be on the output > columns of the UNION. "e.type" etc are names of input-table columns. > Try just "type" and "nom", which will refer to the second and third > output columns of the UNION. > > Pre-7.1 got this wrong (and would sometimes produce wrong output > ordering or even a backend crash, if the arms of the UNION didn't > all yield the same datatype). > > regards, tom lane Hello Tom, Yes, thanks a lot. It works! Regards. -- Richard NAGY Presenceweb --------------788CA54A1D49006E171C7FE2 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane a écrit :
Richard NAGY <richard@presenceweb.com> writes:
> SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu,
> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> aes.sect_id <> 9
> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56
> UNION
> SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id,
> cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes,
> entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and
> aes.sect_id <> 9
> and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and
> aes.ent_id
> not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and
> sect_id <> 3
> and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type,
> e.nom

ORDER BY applied to the result of a UNION has to be on the output
columns of the UNION.  "e.type" etc are names of input-table columns.
Try just "type" and "nom", which will refer to the second and third
output columns of the UNION.

Pre-7.1 got this wrong (and would sometimes produce wrong output
ordering or even a backend crash, if the arms of the UNION didn't
all yield the same datatype).

                        regards, tom lane

Hello Tom,

Yes, thanks a lot. It works!
Regards.

--
Richard NAGY
Presenceweb
  --------------788CA54A1D49006E171C7FE2--