X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C96EE533F4 for ; Tue, 19 Apr 2005 13:58:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59949-10 for ; Tue, 19 Apr 2005 16:58:27 +0000 (GMT) Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by svr1.postgresql.org (Postfix) with ESMTP id 0A3915321C for ; Tue, 19 Apr 2005 13:57:23 -0300 (ADT) Received: from jfradkin ([63.162.100.127]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IF7002HIDRKAI10@vms044.mailsrvcs.net> for pgsql-sql@postgresql.org; Tue, 19 Apr 2005 11:57:22 -0500 (CDT) Date: Tue, 19 Apr 2005 12:57:28 -0400 From: "Joel Fradkin" Subject: Re: trying to do an update a bit confused. In-reply-to: To: =?iso-8859-1?Q?'K=D6PFERL_Robert'?= , Message-id: <000001c54500$df3c1360$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: multipart/alternative; boundary="----=_NextPart_000_0001_01C544DF.582A7360" Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200504/294 X-Sequence-Number: 21187 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C544DF.582A7360 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am not updating 1 record. I have : WHERE m.caseid =3D tblcase.caseid AND m.clientnum::text =3D tblcase.clientnum::text) Which should do the aggregate on the record that is being updated (least = as I understood it). It should update all record in case with either 0 if there are no merchandise records or do a sum of the merch records for the case being updated. =20 Joel =20 I also never heard back on the merge join issue (why do I need to = specify them off on one machine and it runs faster on the other). I am guessing it is memory related and config related, but I am about to give up on postgres as I am just not getting all my views to run fast enough. You guys could say my views are bad SQL design etc, but they run fine in MSSQL. I don=92t mind visiting each one to make them better, but I am = just not able to increase the speed on all of them. The last one about assoc = finally did run in 3 secs with merge joins off which is pretty fast, but now I = have others that seem pretty simple to me and yet run very slow.=20 =20 =20 You're most probably missing a Where clause after the parentensis. see: -----Original Message----- From: Joel Fradkin [mailto:jfradkin@wazagua.com] Sent: Dienstag, 19. April 2005 16:06 To: pgsql-sql@postgresql.org Subject: [SQL] trying to do an update a bit confused. update tblcase set merchandisetotal =3D=20 ( COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS = merchandiseamount FROM tblmerchandise m WHERE m.caseid =3D tblcase.caseid AND m.clientnum::text =3D tblcase.clientnum::text), 0.0) )=20 WHERE ; =20 =20 I tried running the above and it wants to do a sum on all the records in tblcase instead of just the one being updated, what am I doing wrong? =20 Joel Fradkin =20 =20 =20 ------=_NextPart_000_0001_01C544DF.582A7360 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I am not updating 1 = record.

I have = : WHERE m.caseid =3D tblcase.caseid AND m.clientnum::text =3D = tblcase.clientnum::text)

Which should do the aggregate on = the record that is being updated (least as I understood = it).

It should update all record in case = with either 0 if there are no merchandise records or do a sum of the merch = records for the case being updated.

 

Joel

 

I also never heard back on the = merge join issue (why do I need to specify them off on one machine and it runs = faster on the other).

I am guessing it is memory related = and config related, but I am about to give up on postgres as I am just not getting = all my views to run fast enough.

You guys could say my views are bad = SQL design etc, but they run fine in MSSQL. I don’t mind visiting each = one to make them better, but I am just not able to increase the speed on all of = them. The last one about assoc finally did run in 3 secs with merge joins off = which is pretty fast, but now I have others that seem pretty simple to me and = yet run very slow.

 

 

You're most = probably missing a Where clause after the parentensis. see:

-----Original Message-----
From: Joel Fradkin [mailto:jfradkin@wazagua.com]
Sent: Dienstag, 19. April = 2005 16:06
To: = pgsql-sql@postgresql.org
Subject: [SQL] trying to = do an update a bit confused.

update tblcase  set merchandisetotal =3D

(

COALESCE(( SELECT = sum(m.quantity::numeric * m.amount) AS merchandiseamount

    &nbs= p;      FROM tblmerchandise m

    &nbs= p;     WHERE m.caseid =3D tblcase.caseid AND m.clientnum::text =3D tblcase.clientnum::text), 0.0)

) 

WHERE = <condtion that selects one record>;

 

 

I tried running the above = and it wants to do a sum on all the records in tblcase instead of just the one = being updated, what am I doing wrong?

 

Joel = Fradkin

 

 

 

------=_NextPart_000_0001_01C544DF.582A7360--