Interesting point on SQL comparisons

Here's an interesting point regarding SQL in-equality comparisons.

An inequality expression normally looks like this:

where usr_udf_vegan <> '1'

If you work with OIM you will recognize the database field as a User Defined Field (UDF) and the value as the value of a "checked" checkbox which is a string value of '1' - but this is not limited to only OIM.

Now if you try to run this query against a table of values of '1', '0', and NULL, you might see that the NULL values don't get flagged as true.  You would think they would because they are not '1'.

In order to overcome this, add the following:

where (usr_udf_vegan is null or usr_udf_vegan <> '1')

The following also does not work:
where usr_udf_vegan not in ('1')

This specific issue came up with OIM role inequality comparisons and the fix is in the latest bundle patch of OIM.