Thursday, January 17, 2008

NULL is not %null%

APEX Null Value Handling in LOVs (SageLogix Tech Tip).

Everything is NOT IN nothing

Everything is NOT IN nothing (a.k.a. NULL). See the Oracle Documentation on the IN Condition - it says NOT IN [is] Equivalent to !=ALL. Evaluates to FALSE if any member of the set is NULL.

SQL> SELECT 'x' FROM DUAL
2 WHERE 3 NOT IN (1,2,NULL);

no rows selected

Counter-intuitive or what?

Friday, January 04, 2008

Not so overlapping OVERLAPS!

The OVERLAPS operator is counter-intuitive when it comes to the same date:
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod

SQL> select 'x' from dual
2 where (trunc(sysdate),trunc(sysdate)+24)
3 overlaps (trunc(sysdate)+24,trunc(sysdate)+42);

Ingen rækker er valgt


But apparently this is according to the standard (see sql1992.txt) where it says (under "8.11 <overlaps predicate>"):
( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
OR
( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
OR
( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )


Notice the equal in the S2 >= T1 comparison that is negated (i.e. NOT). So be careful out there - know thou standards!