8

I have an odata web service that is returning some null values. I would like to skip these null values. I tried to use the Ne(Not equal) operator to filter the data with null values:

analyticView.xsodata/analyticView?$select=QUANTITY_SOLD,SALE_PRICE&$filter=SALE_PRICE+Ne+null)&$format=json

and I am getting the following error message: 

"Illegal operation 'Ne' at position 11."

I tried also to combine the Not operator with the eq operator in this way: 

analyticView.xsodata/analyticView?$select=QUANTITY_SOLD,SALE_PRICE&$filter=not(SALE_PRICE+eq+null)&$format=json

I keep getting an error message saying: 

value: "No property 'null' exists in type ...

I am using SAP HANA analytic view as a data source, but I thing the issue is not vendor dependent. so, what to do to skip the null values?

Mohamed Ali JAMAOUI
  • 14,275
  • 14
  • 73
  • 117
  • how is `SALE_PRICE` defined in the class? – qujck Oct 15 '13 at 13:57
  • if understand your question, here is how the SALE_PRICE is defined within the metadata of the odata provider – Mohamed Ali JAMAOUI Oct 15 '13 at 14:00
  • 1
    I guess I'm trying to understand if SALE_PRICE is nullable and if this might be the issue because the syntax looks correct and works for me here through the OData found in the `System.Web.Http.OData` namespace. – qujck Oct 15 '13 at 14:06
  • there is a stray bracket in the first example `$filter=SALE_PRICE+Ne+null)&` if that helps? – qujck Oct 15 '13 at 14:06
  • yet another question, to have the null keyword recognized and not generating the error in the second example should my attribute by deliberately set with nullable=true? – Mohamed Ali JAMAOUI Oct 15 '13 at 14:17

2 Answers2

14

The above solutions should work fine, baring in mind that the operators are case sensitive as pointed out by Tne. So in general to filter null values you can use:

  • $filter=(SALE_PRICE+ne+null)
  • or $filter=not(SALE_PRICE+eq+null)

However in the particular case of SAP HANA analytic views the null value is not supported, as a workaround the filters can be defined on the table column at the modeling level of the analytic view. The solution is explained here.

Mohamed Ali JAMAOUI
  • 14,275
  • 14
  • 73
  • 117
6

Note that the operators are case-sensitive. (Use ne instead of Ne.)

The spec does mention that "ISNULL or COALESCE operators are not defined. Instead, there is a null literal which can be used in comparisons" (v3.0, URL Conventions, section 5.1.2.4), which means that it should work.

Indeed, I tried Property ne null without any issue. What's weird is that your attempt with not, although not useful, should have worked. Maybe look at replacing + characters with spaces (possibly encoded), maybe the server is confused.

tne
  • 7,071
  • 2
  • 45
  • 68