Thanks Ann.

Is this the only way?
Will FB2 solve this problem?

My real SP has many parameters:
WHERE OBRA_ID=:OBRA_ID AND DOCUMENTO=:REF_DOCUMENTO AND
TIPO_DOC=:REF_TIPO_DOC AND ANO=:REF_ANO AND ENTIDADE_ID=:ENTIDADE_ID AND
TIPOVALOR=:REF_TIPOVALOR;

So adding all the conditions to is not null will result in a huge where
clause.
And since I use this in many SPs, I will be in a lot of work to do.

Regards,

Luis Madaleno


Ann W. Harrison wrote:

>
> Luis Madaleno wrote:
> > ...
> > UPDATE CONTAS_CORRENTES_FOR SET
> > TOTAL_LIQUIDADO=TOTAL_LIQUIDADO+:LIQUIDADO
> > WHERE OBRA_ID=:OBRA_ID AND DOCUMENTO=:REF_DOCUMENTO;
> > /* the problem is here. if :OBRA_ID is null, this UPDATE fails */
> > END
> > END
> > end^
> >
> > is there any way to force this UPDATE to work when one parameter is
> null?
> >
>
> WHERE (OBRA_ID=:OBRA_ID or :obra_id is null)
> AND (DOCUMENTO=:REF_DOCUMENTO or :ref_documento is null)
> AND NOT (:orbra_id is null and :ref_documento is null);
>
> The last bit keeps two null values from changing the whole table.
>
> Regards,
>
> Ann
>
>

[Non-text portions of this message have been removed]