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 A4AA6535AA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Apr 2005 20:03:39 -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 35825-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Apr 2005 23:03:36 +0000 (GMT)
Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.198])
	by svr1.postgresql.org (Postfix) with ESMTP id CF29853599
	for <pgsql-sql@postgresql.org>; Tue, 19 Apr 2005 20:03:32 -0300 (ADT)
Received: by rproxy.gmail.com with SMTP id j1so25547rnf
	for <pgsql-sql@postgresql.org>; Tue, 19 Apr 2005 16:03:35 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=mLv1/CY7mVhG1eBlA0gXwXF817myQudhKKGVbKddBqWK4m43kUTMGBT91XAdl11e7nXclsSnL3GkV6bJCClVJM52XgJ9BN6UNL2ZwSZ19hRcVdeQF56RqgaXWZv1Ck35cJH4yiHIvmlG+emjSrSQw4HBgKTHUZhJ0CwIpAqJzGQ=
Received: by 10.38.15.7 with SMTP id 7mr169515rno;
	Tue, 19 Apr 2005 16:03:35 -0700 (PDT)
Received: by 10.38.97.29 with HTTP; Tue, 19 Apr 2005 16:03:35 -0700 (PDT)
Message-ID: <c2d9e70e050419160340f7c31d@mail.gmail.com>
Date: Tue, 19 Apr 2005 18:03:35 -0500
From: Jaime Casanova <systemguards@gmail.com>
Reply-To: Jaime Casanova <systemguards@gmail.com>
To: Joel Fradkin <jfradkin@wazagua.com>
Subject: Re: trying to do an update a bit confused.
Cc: pgsql-sql@postgresql.org
In-Reply-To: <000001c544e8$ef28d820$797ba8c0@jfradkin>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <000001c544e8$ef28d820$797ba8c0@jfradkin>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.315 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_ABUSE, RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200504/296
X-Sequence-Number: 21189

On 4/19/05, Joel Fradkin <jfradkin@wazagua.com> wrote:
> =20
> =20
>=20
> update tblcase  set merchandisetotal =3D=20
>=20
> (=20
>=20
> COALESCE(( SELECT sum(m.quantity::numeric * m.amount) AS merchandiseamoun=
t=20
>=20
>            FROM tblmerchandise m=20
>=20
>           WHERE m.caseid =3D tblcase.caseid AND m.clientnum::text =3D
> tblcase.clientnum::text), 0.0)=20
>=20
> )=20
>=20
>  =20

Put the coalesce inside the select:
update tblcase  set merchandisetotal =3D =20
    (SELECT COALESCE(sum(m.quantity::numeric * m.amount),0.0) AS
merchandiseamount
        FROM tblmerchandise m=20
      WHERE m.caseid =3D tblcase.caseid AND m.clientnum::text =3D
tblcase.clientnum::text)

--=20
Regards,

DBA* Jaime Casanova
(DBA: DataBase Aniquilator ;)