Usage of filter on multilevel expand

[RahulYerrabandi76]

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')

[RahulYerrabandi76 · Accepted solution]

Thank you RalfHandl. That worked.

[AlekhyaNemani · Accepted solution]

This example really working ,I also have similar but bit enhanced requirement I need to select the fields of both header and item and also $filter also need to write on both header and Iten

[AlekhyaNemani · Accepted solution]

@RalfHandl

Your solution worked for me also but i have similar requirement with more narrowed search

Here I would like to select specific fields of header based on material T11 but this is filtering is not working getting 400 BAd request error in gateway /sap/opu/odata4/sap/zsales_order_srv/srvd_a2x/sap/zsales_order/0001/SalesOrderHeader?($select= SalesOrder,SalesOrderType)&$expand=_Item($filter=Material eq 'T11';$expand=_ScheduleLine)&$filter=(_Item/any(s: s/Material eq 'T11')

Here I want to filter with soldToParty 12345 and Material T11 and even this is not working

Basically I would like to query by having filter on both item,header,schedule lines and also select only limited fields from all 3 entities

/sap/opu/odata4/sap/zsales_order_srv/srvd_a2x/sap/zsales_order/0001/SalesOrderHeader?($filter=SoldToParty eq '12345')&$expand=_Item($filter=Material eq 'T11';$expand=_ScheduleLine)&$filter=(_Item/any(s: s/Material eq 'T11'). Can Anyone help me is this query is correct