>Hello,
>I would like to use dynamic conditions without an statement like:
>Condition customer can be 0 (all costomers) or inherits a number of a customer
>This doesn't work: select * from table where iif(:input_customer_id <> 0;'customer_id = ' || :input_customer_id,'')
>I can set the input_customer_id to null when its value = 0, perhaps can I realize this with coalesce?
>Where customer_id = coalesce(:input_customer_id;'.') But I can't set = and > 0 at the same time or = coalesce(..) and not null.
>How can I finish my plan? Also I don't like to set some if-statements. if input = 0 etc., there many inputs.

Hi Olaf!

I sometimes use

Where customer_id = coalesce(:input_customer_id, customer_id)

(i.e. always check the customer_id, but if there's no input_customer_id, then check if it is the same at itself.

If customer_id can be NULL, then you could probably replace '=' with 'IS NOT DISTINCT FROM', but I've never tried this.

The main drawback with this solution (I think), is that it can be slow on large tables, so I typically only use this when on smallish tables or if there are other selective criteria in my where clause.

HTH,
Set