SAP odata filters trick: double negative

Say you want to exclude two values of a property in an odata GET URI using an OSQL query with the select-options tables conveniently parsed by SAP from the filter query.  You might think something like this would work:

/sap/opu/odata/SAP/Z_SRV/EntitySet?$filter= Property1 ne ‘FT’ and Property1 ne ‘BN’

You would be wrong.  In fact, SAP doesn’t even pass the select-option filters to your DPC_EXT class GET_ENTITYSET method. “Hmm,” you think, “maybe…”

/sap/opu/odata/SAP/Z_SRV/EntitySet?$filter= not(Property1 eq ‘FT’ or Property1 eq ‘BN’)

Nope!  Again, no filters passed. Maybe…

/sap/opu/odata/SAP/Z_SRV/EntitySet?$filter= Property1 ne ‘FT’ or Property1 ne ‘BN’

Sorry!  The select-options filter is passed, but the results are not what you wanted.

No, it turns out the answer is this:

/sap/opu/odata/SAP/Z_SRV/EntitySet?$filter= not(Property1 eq ‘FT’) or not(Property1 eq ‘BN’)

“But wait!” you say.  “Surely not(Property1 eq ‘FT’) or not(Property1 eq ‘BN’) is equivalent to Property1 ne ‘FT’ or Property1 ne ‘BN’ !”

And that’s the trick… they aren’t.  The SAP parser reads them completely differently.

not(Property1 eq ‘FT’) or not(Property1 eq ‘BN’) results in this:

it’s the equivalent of this:

And guess what  Property1 ne ‘FT’ or Property1 ne ‘BN’ returns? (ABAP veterans will be nodding their heads expectantly about now.)

which, you guessed it, is the equivalent of this:

Obviously, that error does not pop up on odata queries, but it still applies.