When working with odata web services, a given property, for one reason or another, can return null values. I run to a situation where the null value can cause an issue in my application. To picture the issue, imagine that you allow the user to set a maximum number of result row, when the data contains null value you can actually get the whole result set with null values.
The intuitive solution is to skip null value before applying the $top operator to set the maximum number of results. Using the odata standard features, I was able to put together two solutions:
- using the ne (not equal) operator: add a filter on the property containing null values this way, notice that the operator is case sensitive:
$filter=SALE_PRICE+ne+null)
- using explicit not equal operators: with simple logical thinking the ne operator can be replaced with a combination of not and eq operators, the filter becomes:
$filter=not(SALE_PRICE+eq+null)
Both solutions should work without any issues.
Particular case of SAP HANA analytic views
The above solutions won’t work in SAP HANA analytic views exposed by odata, for the simple reason that the null value is not supported, at least for now. In fact, instead of comparing to the null value the odata web service is trying to find a property name with the name null, something that doesn’t and shouldn’t exist in an odata web service. As a result the following error message is thrown:
"No property ‘null’ exists in type
In the case of SAP HANA the solution is to add a filter on the columns with null values at the modeling level. The modeling level is the step of creating the analytic view itself. For instance:
You can tell that this solution only works when the developer have access to the analytic view.
Note: this issue is discussed here in stackoverflow
Next question to study: What if the developer doesn’t have access to the analytic view modeling ?
Thanks, peace.
Intelligea 