SAP HANA formula expression excludes Null values together with specific value

[former_member608099]

Hi all,

Filter expression in SAP HANA view excludes also NULL values when you want to exclude only specific value.
For example you get values 'X', 'Y' and NULL for the field.

Filter experssion ("field" != 'X') will exclude not only 'X' as expected but NULL values also and retrives only 'Y'.

Is that a bug or expected behavior?

Thank you,
Alena

[KonradZaleski · Accepted solution]

Try:

("field" != 'X') or (isnull("field"))

[former_member608099 · Accepted solution]

Thank you, Konrad! That works!

So this is an expected behavior then...

However, honestly, I don't understand the logic, why exclusion of exact specific value excludes also NULL

[KonradZaleski · Accepted solution]

This is because null is not a value by itself. Null means "unknown", so it's neither equal X nor not equal X - it's just undefined.

[former_member722]

I just realized this recently

According to this documentation, this is not unique to HANA Databases

https://launchpad.support.sap.com/#/notes/0001807542