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.
Hey David,
Thanks a lot for your post, I found while it trying to solve the same issue!
Have no idea how you came up with this idea, as obviously sap is not handling that case properly.
Unfortunatelly, OdataUtils which prepares $filter operator expression for smart controls does not generate such string, so we decided to modify select options that contain NE operator from "I NE + I NE" to "E EQ + E EQ" on the backend side, which, of course, is a little bit dirty, but works fine for any frontend.
I'll also leave these links here for anyone who is concerned with this issue:
SAP note 1671893 about $filter to ABAP select options conversion
SAP note 1574568 about known Gateway constraints
and stackoverflow discussion on that topic - How to exclude multiple values in OData call?
Regards, Alex