Support for wild card search in SAP Gateway based Odata services

[former_member213462]

I understand there are some constraints with SAP Odata implementation, especially around the $filter command. Is it possible to do wildcard searches with Odata services implemented on SAP Gateway? One of the use cases we have is to look up customer account by customer name, for example a query that looks something like URL below.

https://<server>:<port>/sap/opu/odata/sap/ZCUSTOMER_HELP_SHIP_TO_SRV/Customerhelp_shiptoSet?$filter=...

When we execute this query on SAP Gateway we get an error that states "contains" is invalid command with an SAP note that lists SAP's Odata implementation limitations.

Any suggestions on how we can get around this limitation?

Thanks in advance for your response!

[Former Member · Accepted solution]

Hi Sri,

I have personally worked successfully with the URL option startswith. For example access to a service of the Gateway Service Consumption Demo System:

https://sapes1.sapdevcenter.com/sap/opu/odata/sap/ZGWSAMPLE_ SRV/BusinessPartnerCollection?$filter=startswith(CompanyName,'S')

Even more interesting for you is the URL option substring. Just as with startswith, the Gateway Framework will convert this into a string in import parameter IV_FILTER_SELECT_OPTIONS of the method *_GET_ENTITYSET of the *_DPC_EXT class of your entity set. You can use the parameter within a WHERE clause of a SELECT statement when implementing _GET_ENTITYSET. However, please check what you receive in IV_FILTER_SELECT_OPTIONS for all required cases beforehand, using the debugger!

SELECT * FROM your_table

INTO TABLE lt_your_target

WHERE iv_filter_select_options.

If that is not sufficient you can check what you receive in the import parameter IT_FILTER_SELECT_OPTIONS, which you can use as a selection table in a SELECT statement or in any function module which takes a selection table as input parameter.

If you have the implementation generated by RFC/BOR mapping, then startswith and substring will only work with the generated code if the function module has range tables as input parameters and you mapped these input parameters.

There are two ways of using substring. Both take the starting position of your pattern within the attribute value which is searched - please see SAP Note 1922246 for details. The first option:

$filter=substring(FirstName, 1 ) eq 'amina'

-> Generated filter condition: NAME_FIRST LIKE '_amina'

The second option additonally takes the number of characters which must be equal:

$filter=substring(FirstName, 1 , 3 ) eq 'ami'

-> Generated filter condition: NAME_FIRST LIKE '_ami%'

Best regards,

Ringo

[former_member213462 · Accepted solution]

Hi Ringo ~ Thanks very much for your prompt reply and detailed explanation of the options to do wild card searches in Gateway. Your suggestions have been very helpful. I have been able to successfully use URL options startwith. In the process of testing URL option substring, I believe some authorization issue is preventing me from executing this call as I get a valid response but no data. Working with Security to resolve the authorization issue and will provide an update soon.

Regards,

Sri

[mike_reader3 · Accepted solution]

Ringo,

I am attempting to use starts with but it does not seem to be getting passed to the filters.

Is this version dependant? I found some documentation stating that we cannot use startswith in Gateway calls - but it was an older article so, who knows.

Thanks, Mike

[jagesh_lakdawala · Accepted solution]

Hi Mike,

which version you are using? I am on SAP NW 7.4 hub system with the component SAP_GWFND with SPS 008 and for me  startswith function is working fine.

as Ringo mentioned, I tried for the substring function but in debugging I don't see filter string is getting build so may be some issue with the substring function.

trying the URL as

Ringo, please suggest.

Regards,

Jagesh

[mike_reader3 · Accepted solution]

Jagesh,

I found the issue, I was using this example from Odata.org

http://services.odata.org/Northwind/Northwind.svc/Customers?$filter=startswith(CompanyName, 'Alfr') eq true

the 'eq true' was causing the problem - I removed it and it works fine.

-Mike

[fischerm]

Hey Community,

does this works also for OData services created via CDS views?

In my case it is not working...

Here is an example: .../sap/opu/odata/sap/ZI_TEST_CDS/ZI_TEST?$filter=substring(testfield1,'test')
Kind regards
Markus