Be careful when using EXCLUDE clause together with operator "equals" in Groups

You can expect strange results when you are using clause EXCLUDE together with operator "Equal" in Groups.

Generally, the fastest fix to this : Instead of using "Exclude if X==14", better do "Include if X!= 14" (include if X not equal to 14).


To remember:

« EXCLUDE » + « EQUAL » works fine!

Exemple : « Exclude if FirstName = Anton » will exactly exclude all Antons

But « EXCLUDE » + « NOT EQUAL » which causes a problem.

Example: « Exclude if FirstName != Anton ».


From the point of view of SQL database, when a field X is NULL and you ask a question like :"is field X non equal to ABC", the answer you will get is NO.
This is really counter-intuitive but this is SQL logic.
In SQL, "NULL" doesn't mean "Empty" but it means "I don't know the value, it could be anything"
Therefore any comparison to a "NULL" value will be necessary : NO (false).

Therefore, in our Ofsys groups, we have included what it takes to escape this situation and include "NULL" as being matches to "NO EAQUL TO"

So, if, in Ofsys, you say: X <> 14 (X not equal to 14), we convert the query as: … AND ( ( x <> 14 ) OR (x is null) )
But in the contrary, you say: X == 14, the we don't alter it since "NULL" will answer "False" which is the desired answer.


But if you say « Exclude if X == 14 » then in SQL it gives this: … AND NOT ( X == 14 )


So because of this SQL logic, if you can avoid using the EXCLUDE clause together with operator EQUAL, you'll get accurate results.






Please sign in to leave a comment.