On Fri, Jan 23, 2009 at 06:32:17PM +0300, Igor Katson wrote:
> Sam, I'm not sure if this is correct to do that, as you I don't want to
> remember what will happen, if you use NULL = NULL or upper(NULL) etc.:
>
> WHERE
> COALESCE(city_id = i_city_id, TRUE) AND
> COALESCE(edu_id = i_edu_id, TRUE) AND
> COALESCE(upper(firstname) ~ upper(i_firstname), TRUE) AND
> COALESCE(upper(lastname) ~ upper(i_lastname), TRUE)
I'm not quite sure what you mean when you say "I don't want to remember
what will happen". Here is a state table of the various options you've
presented:
param tbl Sam's Your's/Michael's
NULL NULL TRUE NULL
NULL 0 TRUE TRUE
NULL 1 TRUE TRUE
0 NULL TRUE NULL
0 0 TRUE TRUE
0 1 FALSE FALSE
1 NULL TRUE NULL
1 0 FALSE FALSE
1 1 TRUE TRUE
The "tbl" column is the value for, say, edu_id; the "param" is the
matching parameter value, say i_edu_id. "Sam's" is the output of doing a
COALESCE and the "Your's/Michael's" column is the output of doing your
original CASE statement or Michael's OR variant. The thing to note are
the extra NULLs in your variant as these will cause any row with a NULL
value in the table to never get returned. This may, or may not, be what
you want!
--
Sam http://samason.me.uk/