Hi all,

I'd like to write a single JDBC statement that can handle the equivalent of any number of NOT BETWEEN date1 AND date2 where clauses.

By single query, i mean that the same SQL string will be used to create the JDBC statements and then have different parameters supplied.

This is so that the underlying frameworks can efficiently cache the query (i've been stung by that before).

Essentially, I'd like to find a query that is the equivalent of

SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ?

and at the same time could be used with fewer parameters:

SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ?

or more parameters

SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ?

I will consider using a temporary table if that will be simpler and more efficient.

thanks for the help!