NULL-ness checks


From a discussion on the news group recently. The issue was the most efficient way to compare two string fields where we wanted to over-ride the Oracle handling of NULL's, namely, a null never is 'equal' to another null.

Thus from a SQL perspective, there appeared to be two options

In the second case, the special_char would need to be a value chosen that we know columns 'a' and 'b' can never possibly acquire. The problem here is choosing this special character.

Jonathan Lewis suggested a workaround to this issue by using the undocumented function SYS_OP_MAP_NONNULL, which appears to return an impossible value when the passed parameter is null. Thus the predicate would simply become:

where sys_op_map_nonnull(a) = sys_op_map_nonnull(b)

See the full newsgroup post here