Question Re: Filter null values in date field in Technology Q&A

Filter null values in date field

fabian_zimmermann — Wed, 15 Dec 2021 09:26:10 GMT

Hello,

I am running a capire odata instance on SAP BTP and want to filter for entities that "do not have a date", i.e. where the date field is null.

This request gives me an empty result

Request: GET /EntitySet?$filter=DateProperty eq null

Result: {"@odata.context":"$metadata#EntitySet","@odata.count":0,"value":[]}

The opposite request includes all records, including the ones that have null for that property

Request: GET /EntitySet?$filter=DateProperty ne null

Result: {"@odata.context":"$metadata#EntitySet","@odata.count":5093,"value":[{...,DateProperty":null,...},...]

When uppercasing null (NULL) it says that a property NULL is not available - which tells me that the system does recognize null as a valid value for the date field...

Therefore I am a little bit lost.

Could you give me some guidance?

Thank you.

Regards,

Fabian

Re: Filter null values in date field

Ivan-Mirisola — Wed, 15 Dec 2021 16:01:40 GMT

Hi fabian.zimmermann,

Have you tried this:

GET /EntitySet?$filter=(DateProperty+eq+null)
GET /EntitySet?$filter=not(DateProperty+eq+null)

Or, how about adding an entity as a projection of your original entity with a select statement on your service definition, like so:

service GenericService {
  entity EntitySet as a projection on MyDataModelEntity;

  entity DatelessEntitySet as select from EntitySet {*}
	where DateProperty is null;

This way you would be simplifying your OData requests at the UI level while avoiding to have excessive/unecessary filters in the first place.

Then you would be able to get it directly without any filter expressions.

GET /GenericService/DatelessEntitySet

Please read more here:

https://blogs.sap.com/2019/03/11/sap-cloud-platform-backend-service-tutorial-9-cds-how-to-use-select-statement/

Best regards,
Ivan

Re: Filter null values in date field

fabian_zimmermann — Wed, 15 Dec 2021 16:23:24 GMT

Hello Ivan,

thank you for your answer!

The result with parantheses is identical (i.e. same problem). The "not" does not even work. It is interpreted as a property by the Capire odata service.

The suggestion with the entity projection would not work in my case, I think. My application behaves like a pivot table. The user selects the fields to use in his table (or chart), sets the filters and so on. I would have to make a projection for every field in my data model (and then the user would probably not be able to combine that field with others).

Regards,

Fabian