Usage of filter on multilevel expand

[RahulYerrabandi]

Hi All,

I have multiple level entities Order header/Item/Schedule.

I am trying to filter based on Material which is at item level. But, I am getting orders headers for which there are no items at all. My requirement is that if there are no items with the given material, the order header should also be not displayed.

Tried below two options

1) This is giving all the header records, even those which do not have any item with material '123'. Item entities are blank in such cases

/sap/opu/odata4/sap/zsales_order_srv/srvd_a2x/sap/zsales_order/0001/SalesOrderHeader?$expand=_Item($expand=_Schedule)&$filter=(_Item/all(s: s/Material eq '123')

2) This is giving only the headers which has at least one item with material  '123'. However it is getting all the other items in the orders whose item is something else.

/sap/opu/odata4/sap/zsales_order_srv/srvd_a2x/sap/zsales_order/0001/SalesOrderHeader?$expand=_Item($expand=_Schedule)&$filter=(_Item/any(s: s/Material eq '123')

Request you to help me with the right filter and expand options

[RalfHandl · Accepted solution]

Assuming 2) returns all headers you want, and you only want to have items with material '123', you can extend the query with a nested filter on items:

/sap/opu/odata4/sap/zsales_order_srv/srvd_a2x/sap/zsales_order/0001/SalesOrderHeader?$expand=_Item($filter=Material eq '123';$expand=_Schedule)&$filter=(_Item/any(s: s/Material eq '123')

[RahulYerrabandi · Accepted solution]

Thank you RalfHandl. That worked.