Re: trying to do an update a bit confused.

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

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

--
Regards,

DBA* Jaime Casanova
(DBA: DataBase Aniquilator ;)