Issue with OData $filter for entity mapped to standard CDS

[tudor_teoteoi]

Dear community,

I build a very basic help for material groups, which is mapped to I_MATERIALGROUPTEXT.

Now I am trying to filter entities from MaterialGroups entity using the OData standard paramter $filter.

If I use substringof('rija',ShortDescription) as a condition, I get 4 records.

If I use substringof('DE',Language) as a condition, I get tens of records.

If I combine the two using or, I only get the first 4 records. The entity and parameter syntax in this case is: MaterialGroups?$filter=substringof('rija',ShortDescription) or substringof('DE',Language).

This seems like a bug to me. Is there anything obvious that I am missing? Since this is mapped to an SAP view, the underlying processing is done using SAP standard code.

I am using the Gateway client to test (transaction /IWFND/GW_CLIENT).

Thanks and have a nice day,

Tudor

[former_member461611]

Hello Tudor,

Since you are trying to combine two different properties, use and instead of or.

Please check.

Regards,

Amodh

[tudor_teoteoi]

Hello John,

I'm not sure why the front-end programmer needs or instead of and, as the latter is more usual indeed, especially when handling different fields.

However, I checked how the or works for entities that I manually implemented, where I use this line in the GET_ENTITYSET method:
lv_where_filter = io_tech_request_context->get_osql_where_clause( ).

For a filter condition of
substringof('ojec',Title) or substringof('USE',CreatedBy)
I do get a correct WHERE condition for the SELECT
( PRJTI LIKE '%ojec%' OR CREBY LIKE '%USE%' ).

So I don't why SAP couldn't do that in their own code...

Regards,

Tudor

[former_member461611]

Hello Tudor,

Thanks for the response.

In custom entities we can develop the logic as you have mentioned. But whenever you are trying to execute

MaterialGroups?$filter=substringof('rija',ShortDescription) or substringof('DE',Language) this query, the select option only accepts operator and with two different properties. However with same properties or will works fine( substringof('X',ShortDescription) or substringof('Y',ShortDescription) ).

Please check for more info.

Regards,

Amodh

[bharatpemmireddy]

when i used below I got correct count

/sap/opu/odata/sap/ZTESTFILTER_SRV/I_MaterialGroupText?$format=json&$filter=substringof('rija',MaterialGroupName) or Language eq 'DE'&$inlinecount=allpages.

we are on S4HANA 1909

[bharatpemmireddy]

Just debugged the standard code for

/sap/opu/odata/sap/ZTESTFILTER_SRV/I_MaterialGroupText?$format=json&$filter=substringof('rija',MaterialGroupName) or substringof('DE',Language)

and this is where the select query executes.

The problem is with the generated where clause which is below

( %_native_pred( hdb = `%_native( 'UPPER(' ), I_MATERIALGROUPTEXT~MATERIALGROUPNAME, %_native( ')' ), %_native( 'LIKE' ), %_native( 'UPPER(' ), '%rija%', %_native( ')' )` ) OR %_native_pred( hdb = `%_native( 'UPPER(' ), I_MATERIALGROUPTEXT~LANGUAGE, %_native( ')' ), %_native( 'LIKE' ), %_native( 'UPPER(' ), '00', %_native( ')' )` ) )

As you can see 'DE' is not passed anywhere. This is a bug in SAP. But you can use the URL construct I have shared earlier. Another option is to use CDS view "C_MM_MaterialGroupValueHelp", but this will give only in system logon language.

[tudor_teoteoi]

Hello Bharat,

Thanks for your analysis and for spending time to even debug the issue.

I'll mark your answer as the valid one, as I now have a confirmation that there is an SAP bug in there.

We are also using HANA1909. I hope your workaround will work in all cases.

Kind Regards,

Tudor