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

**SHORT EXPLANATION:**

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 ».

**LONG EXPLANATION:**

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.

## 0 Comments