Received: from presence1.observatoiredesmarques.fr (presence1.observatoiredesmarques.fr [212.73.199.68]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EAKD700389 for ; Fri, 14 Sep 2001 06:20:13 -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 KAA30559 for ; Fri, 14 Sep 2001 10:21:10 GMT Message-ID: <3BA1D7DD.569B218C@presenceweb.com> Date: Fri, 14 Sep 2001 12:11:41 +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: pgsql-sql@postgresql.org Subject: SQL request change when upgrade from 7.0.2 to 7.1.3 Content-Type: multipart/alternative; boundary="------------A4989322FF2BF91F3036FD06" X-Archive-Number: 200109/73 X-Sequence-Number: 4719 --------------A4989322FF2BF91F3036FD06 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hello, Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But, now, one request which was 'good' before, don't want to work any more now. It was : (pretty long) 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 Now, if I want that my request works well, I have to remove the order by statement. But, of course, it is not ordered any more. So how can I translate this request to one which can work with an order by statement ? Thanks. -- Richard NAGY Presenceweb --------------A4989322FF2BF91F3036FD06 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hello,

Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But, now, one request which was 'good' before, don't want to work any more now.

It was : (pretty long)

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

Now, if I want that my request works well, I have to remove the order by statement. But, of course, it is not ordered any more.

So how can I translate this request to one which can work with an order by statement ?

Thanks.

--
Richard NAGY
Presenceweb
  --------------A4989322FF2BF91F3036FD06--