>But what I really want, doesn't work:
>
>select col1 from table1 where (col 1 = ? and ? is not null)
>select col1 from table1 where (col 1 = ? or ? is null)
>
>So I can test a column against a parameter, and I can test a parameter
>against a value, but what I need is to test a column against a parameter
>unless the parameter is null. And for some reason I can't test a
>parameter to see if it's null.

Hi Kurt!

select col1 from table1 where (col 1 = ? and ? is not null)

is identical to

select col1 from table1 where col 1 = ?

since nothing is equal to NULL, not even NULL.

Your other query can be more tricky. In some cases you can simply use:

select col1 from table1 where col1 = coalesce(?, col1)

but it will not work if col1 is NULL since NULL <> NULL. I don't (yet) know whether

select col1 from table1 where col1 IS NOT DISTINCT FROM coalesce(?, col1)

will get the rows you want.

I once had a very different problem with a solution that also might be of help (IS NOT DISTINCT FROM is slightly simpler if that works).

WITH TMP(Param1) AS
(SELECT CAST(? AS INTEGER) FROM RDB$DATABASE) /*INTEGER is just an example, use the same field type as col1*/

select col1 from table1 t1
cross join tmp t2
where t1.col1 = t2.Param1 or t2.Param1 is null

HTH,
Set