TOracleQuery with NULL variables

[nrsgzz]

Hi all.
I have a query that uses variables, something like this

SELECT *
FROM
MYTAB A
WHERE
A.COL1 = :COL1
AND
A.COL2 = :COL2

However the variables could be null and in that case the query will never return any results.
How can I make it work?

 

[Marco Kalter]

Comparing with NULL will resolve to NULL, which is neither True nor False.

You can change the query as follows:

SELECT *
FROM
MYTAB A
WHERE
(A.COL1 = :COL1 OR (A.COL1 IS NULL AND :COL1 IS NULL))
AND
(A.COL2 = :COL2 OR (A.COL2 IS NULL AND :COL2 IS NULL))