23

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
John Isaiah Carmona
  • 5,260
  • 10
  • 45
  • 79
  • 3
    IF DB2 has "ANSI NULL" semantics then the only binary operator that is true should be "IS NULL", AFAIK. –  Mar 09 '12 at 06:33
  • 1
    @pst DB2 [has ANSI](http://www.dbforums.com/db2/765612-handing-null-values-db2.html#post2808106), what do you mean by _the only binary operator that is true should be "IS NULL"_ ? – John Isaiah Carmona Mar 09 '12 at 06:45
  • 1
    `isnull` is a unary operator... – nomen Sep 17 '19 at 16:16

3 Answers3

18

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
Eggi
  • 1,684
  • 4
  • 20
  • 31
  • 1
    Thanks for answering, but it seems a little dimmed to me. It says there: `null is never equal to any other value`, Is _equal_ the same with _greater than_ condition? Please help. – John Isaiah Carmona Mar 09 '12 at 06:43
  • I edited my answer to make clear that all comparisons fail if one or both sides of the comparison have null included (except for IS/IS NOT NULL). – Eggi Mar 09 '12 at 06:49
  • 1
    Super duper ultra mega thank you very much for your help, but what do you mean by _fail_, as in the query will throw an error? – John Isaiah Carmona Mar 09 '12 at 06:53
  • 1
    It will return no result. The comparison is evaluated as false (thats what i meant with fail). – Eggi Mar 09 '12 at 07:24
  • "That means that all comparison operations will be false because you are comparing an unknown value to something." You have misunderstood some fundamentals. First, null is not a value, rather it is a place-holder for a value that is expected but currently missing (it might be for reasons 'unknown', 'does not apply', 'has not yet occurred', etc but it is the application, not the presence of a null, which tells us that). – onedaywhen Mar 09 '12 at 11:57
  • 1
    Second, you yourself point out another comparison with null that can evaluate to TRUE e.g. `IS DISTINCT FROM`. Third, `greater than` comparisons involving nulls evaluate to UNKNOWN e.g. `SYSDATE > NULL` evaluates to `UNKNOWN`. Loosely speaking, in SQL DML this will cause the row to be removed from the result but in SQL DDL will result in the constraint being satisfied and the update succeeding. Null handling is non-intuitive and error prone: I strongly suggest you avoid nulls entirely. – onedaywhen Mar 09 '12 at 12:05
  • I said nothing about null being a value?! I already said that it is unknown but what I said was that the comparison is incorrect (so null compared to something is false). So I don't see a mistake there. – Eggi Mar 09 '12 at 12:19
14

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.

Vince
  • 1,570
  • 3
  • 27
  • 48
1

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
Klyuch
  • 66
  • 1
  • 6