Luis Madaleno wrote:

>
>> 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);
>>

> Is this the only way?

I don't completely understand the semantics of your query.
If your input value is null, do you want to match all records
that meet the other criteria? Can the field values be null?
Do you want to match null fields to null inputs or null inputs
to any field value.

If you want to match null inputs and field values, you'd be
better off changing the fields to disallow nulls and use some
specific value (e.g. -1) to indication an absence of a value
and set the input to that value if the input is not supplied.

> Will FB2 solve this problem?

FB2 has a new predicate <value> IS [NOT] DISTINCT FROM <value>
which is true of the values are not null and equal or if both
values are null.

>
> 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.

Sorry, but the rules about NULL handling are quite clear and we're
not about to abandon the standard rules.

Regards


Ann