In this blog, I will concentrate only on the OData query options which are not available easily or at least my search patterns were not able to unearth them and those which are worth mentioning for future references. There are already lots of blog on OData query options and lot of documentations are available. For starter please refer to OData Query Options @ SAP Help and Basic tutorial from OData.org .
Note: Public OData Service Northwind is used for samples
- $filter
- Service Collections ( List all available OData Services in the Gateway system )
- https://<hostname>:<port>/sap/opu/odata/IWFND/CATALOGSERVICE;v=2/ServiceCollection
- Useful Links
Getting Count with Filter ($count in combination with $filter)
URL for Total Count:
https://services.odata.org/V3/Northwind/Northwind.svc/Products/$count
Now we will count with filter for CategoryID = 1
URL for Count with Filter :
https://services.odata.org/V3/Northwind/Northwind.svc/Products/$count?$filter=CategoryID eq 1
At the time of creating this blog, total count for Products in Northwind service is 77 and count with filter CategoryID = 1 is 12.
Getting Exclusive (‘E’) sign in ABAP Select Options using $filter
ABAP Select Option allow two Signs Inclusive (I) to include the values or Exclusive (E) exclude the values.
We will use OData $filter query option to exclude the values in result set.
URL to Exclude a value in Filter
https://services.odata.org/V3/Northwind/Northwind.svc/Products?$format=json&$filter=not(CategoryID eq 1)
URL to Exclude multiple values in Filter
$filter=not(CategoryID eq 1) or not(CategoryID eq 2)
This will not work in Northwind Service as Northwind is not SAP based service.
For Northwind you have to use $filter=not(CategoryID eq 1) and not(CategoryID eq 2)
But it will work in SAP as SAP allows only OR operator for same column.
In backend system ABAP Select Option will be realised as below for CategoryID
| Sign | Option | Low | High |
| E | EQ | 1 | |
| E | EQ | 2 |
SubstringOf , StartsWith, EndsWith
SubstringOf:
https://services.odata.org/V3/Northwind/Northwind.svc/Products?$format=json&$filter=substringof(‘ha’,ProductName)
StartsWith:
https://services.odata.org/V3/Northwind/Northwind.svc/Products?$format=json&$filter=startswith(ProductName,’Ch’)
EndsWith:
https://services.odata.org/V3/Northwind/Northwind.svc/Products?$format=json&$filter=endswith(ProductName,’rs’)
In backend system ABAP Select Option will be realised as below for ProductName
| String Operation | Sign | Option | Low | High |
| SubstringOf | I | CP | *ha* | |
| StartsWith | I | CP | Ch* | |
| EndsWith | I | CP | *rs |
Using special character ‘&’ or Apostrophe in string search
‘&’ is used as part of OData query operation to bind different query together, therefore using ‘&’ in string search is tricky.
We have to pass the ASCII value of the same in single quote.
E.g. If we have to search M&M in SupplierName M&M Company then $filter=substringof(‘M%26M’,SupplierName)
Same goes for SingleQuote or Apostrophe, you can use single quote two times.
E.g. If we have to search ma’ in SupplierName Grandma’s Company then $filter=substringof(‘ma”’,SupplierName)