I have this query in DB2
SELECT * FROM SOMESCHEMA.SOMETABLE WHERE SYSDATE > @A
If the SYSDATE is NULL, would it be greater than any value of @A, assuming that @A and SOMESCHEMA.SOMETABLE.SYSDATE is a Date data type?
Please help. Thanks in advance.
Another predicate that is useful for comparing values that can contain the NULL value is the DISTINCT predicate. Comparing two columns using a normal equal comparison (COL1 = COL2) will be true if both columns contain an equal non-null value. If both columns are null, the result will be false because null is never equal to any other value, not even another null value. Using the DISTINCT predicate, null values are considered equal. So COL1 is NOT DISTINCT from COL2 will be true if both columns contain an equal non-null value and also when both columns are the null value.
DB2 Null Handling
That means that all comparison operations will be false because you are comparing an unknown value to something. So no matter which comparison you use (only the IS NULL/IS NOT NULL operation do work!), it will be false.
If you want the query to work you should use something like
SELECT *
FROM SOMESCHEMA.SOMETABLE
WHERE COALESCE(SYSDATE, TIMESTAMP_FORMAT('0001-01-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) > @A
Another possibility is to use IS NULL to test if a value is null:
SELECT * FROM SOMESCHEMA.SOMETABLE WHERE SYSDATE > @A OR SYSDATE IS NULL
will include the value in your set of returned values, instead of COALESCE function. It works only with simple cases though.
You can use this solution for comparing of two nullable dates (P.EndDate,C.EndDate):
[MinDate] =
CASE
WHEN
ISNULL(C.EndDate,P.EndDate) <= ISNULL(P.EndDate,C.EndDate)
THEN
ISNULL(C.EndDate,P.EndDate)
ELSE
ISNULL(P.EndDate,C.EndDate)
END
