$filter with 'and' / 'or' options for select query

[Former Member]

Dear Experts,

I have following query to handle filter options in entityset, and in the entityset, filter parameters are filled in the importing parameter - IT_FILTER_SELECT_OPTIONS and I have logic based on this internal table. And it works fine.

/sap/opu/odata/sap/ZBW_CUSTSEL_SRV/CUSTOMERSet?$filter=( Customer eq '32' and Name eq 'AT' )&$orderby=Name&$format=json

But When I use 'OR' instead of 'AND' then the system does not fill the filter parameters in IT_FILTER_SELECT_OPTIONS. What is the reason for this behavior.

I know that in both cases IV_FILTER_STRING filled, but then I have to slit this string and make sure of operators so on.

Thank you,

Murali

[AshwinDutt · Accepted solution]

Hello Murali,

When you are passing different fields as part of Filter parameters you need to use AND only ( which is as below as per your URL )

?$filter=Customer eq '32' and Name eq 'AT'

When you want to pass multiple values for the same fields as part of Filter parameters you can use OR in combination with AND as below

?$filter=( Customer eq '32' or Customer eq '33' ) and ( Name eq 'AT' or Name eq 'BT' )

?$filter=( Customer eq '32' or Customer eq '33' )

?$filter=( Name eq 'AT' or Name eq 'BT' )

Regards,

Ashwin

[Former Member · Accepted solution]

Hi Ashwin,

Thank you for your detailed explanation. But what if user wants to get results from different fields with OR operator. for example, if we would like to have customer = 32 also Customer name = 'AT'.

Why does the operator 'OR' is not interpreted for different fields? Is there a logical reason or design reason for it?

Alternative: I am using 'AND' and in the back-end preparing 'OR'.

Best Regards,

Murali Pinnamaneni

[AshwinDutt · Accepted solution]

But what if user wants to get results from different fields with OR operator. for example, if we would like to have customer = 32 also Customer name = 'AT'.

For this you will still be able to work by sending with AND right as below ?

I see what you are saying is same as below

?$filter=Customer eq '32' and Name eq 'AT'

[Former Member · Accepted solution]

May be my interpretation is wrong. I am interpreting them as SQL query as it is. Thanks for reply Ashwin.

[mike_reader3 · Accepted solution]

I am not sure I 100% agree with the answer on this one.

If I wanted to search where customer eq 32 and name eq 'AT' I would assume I can use this:

/sap/opu/odata/sap/ZBW_CUSTSEL_SRV/CUSTOMERSet?$filter=( Customer eq '32' and Name eq 'AT' )&$orderby=Name&$format=json

If I wanted to search where customer eq 32 or name eq 'AT' I would assume I can use this

/sap/opu/odata/sap/ZBW_CUSTSEL_SRV/CUSTOMERSet?$filter=( Customer eq '32' or Name eq 'AT' )&$orderby=Name&$format=json

These, one would think, should return different result sets (the first one being more restrictive).

I have run into the exact same issue as the original issue in this thread and I am wondering if it is a limitation of Odata2.0 and or SAP Gateway?

[mike_reader3 · Accepted solution]

Example from OData.org:

http://host/service/Products?$filter=Name eq 'Milk' or Price lt 2.55

Perhaps this is a limitation on Gateway, when we use the IT_FILTER_SELECT_OPTIONS table, there would be no way of determining if we want to do an AND or an OR because each parameter is stored in its own table with the Sign / Option / Low / High values.

I am not sure how Gateway would differentiate between:

/sap/opu/odata/sap/ZBW_CUSTSEL_SRV/CUSTOMERSet?$filter=( Customer eq '32' or Name eq 'AT' )&$orderby=Name&$format=json

and

/sap/opu/odata/sap/ZBW_CUSTSEL_SRV/CUSTOMERSet?$filter=( Customer eq '32' and Name eq 'AT' )&$orderby=Name&$format=json

by using the IT_FILTER_SELECT_OPTIONS table?

Interested in other peoples thoughts on this.

-Mike

[Former Member · Accepted solution]

Hi Mike, My understanding is Odata does not interprete AND / OR (like DB SQL statements), if you want multiple conditions to be passed then use AND, so that it captures all conditions in IT_FILTER_SELECT_OPTIONS otherwise if you use OR then you only have IV_FILTER_STRING filled with you filters. Best Regards, Murali

[mike_reader3 · Accepted solution]

Hi Murali,

I did notice that IV_FILTER_STRING had our condition, I am not sure it is feasible to parse through that to come up with our parameters.

I believe pure Odata gives us the option to use AND and OR properly as shown from my example at Odata.org:

http://host/service/Products?$filter=Name eq 'Milk' or Price lt 2.55

What we seem to be facing is a limitation in how the parameters are interpreted on the SAP side due to the use of the select options table (IT_FILTER_SELECT_OPTIONS).

We have to decide we if want to do an AND or an OR with our select and not base it on the filters being passed.

-Mike