Skip to Content
Author's profile photo Carlos Roggan

SAP Cloud Platform SDK for service development: Create OData Service [15] Data Source OData V2: QUERY advanced

Welcome to our next round of playing ‘round with the SAP Cloud Platform SDK for service development, to expose OData v4 service, while using the data source API to consume data from an OData v2 service.

In the previous round we learned basic implementation for the 5 supported operations.
Let’s continue now with some more capabilities offered by the data source API for OData v2 consumption.
We’ll focus on the QUERY operation and we’ll make the call to the consumed a bit more sophisticated.

BTW, this blog is part of the series of tutorials for beginners.


Let’s skip the Prerequisites and Preparation section, it is the same like in previous tutorial.
Instead, let’s directly discuss what we want to do.

  • We want to develop an OData v4 service which simplifies access to backend data
  • In the backend we have BusinessPartners, Contacts and Products (among much more other data)
  • We want to expose only those business partners who are suppliers.
  • We want only few basic pieces of information
  • Along with the suppliers, we want to expose their products
  • From the products we expose only basic info
  • In addition, we expose some data which we calculate ourselves
  • Also, we want to expose the supplier’s main contact person, but only basic info

That’s the high-level description of our mission.
How it is technically realized?
We’ll see.

Let’s skip the Project Creation, it is always the same.
Let’s skip the model…? Oh no, we need a new model:



In the present tutorial, we’re focusing on suppliers:

<EntityType Name="Supplier">
		<PropertyRef Name="SupplierIdentifier" />
	<Property Name="SupplierIdentifier" Type="Edm.String" />
	<Property Name="SupplierCompanyName" Type="Edm.String" />
	<Property Name="SupplierCity" Type="Edm.String" />
	<Property Name="SupplierCountry" Type="Edm.String" />
	<Property Name="ContactFirstName" Type="Edm.String" />
	<Property Name="ContactLastName" Type="Edm.String" />
	<Property Name="ContactGuid" Type="Edm.Guid" />
	<Property Name="ContactBirth" Type="Edm.Date" />
	<Property Name="ProductsToSupply" Type="Edm.String" />
	<Property Name="ProductsTotalAmout" Type="Edm.Int16" />
	<Property Name="ProductsContainAds" Type="Edm.Boolean" />
	<Property Name="ProductsTotalValue" Type="Edm.Decimal" Scale="2" Precision="8" />



As mentioned above, we want to focus on the QUERY operation, in order to learn about some interesting capabilities.

Let’s go through our task list.


Simplify the model

This is represented in our edmx file.
The 3 backend entities are merged into one entity and the property names are renamed

Below table tries to summarize the modifications:


Our v4 entity Our v4 property name Backend v2 entity Backend v2 property name
Supplier BusinessPartner
 SupplierIdentifier  BusinessPartnerID
(used for $select and $filter)
 SupplierCompanyName  CompanyName
 SupplierCity  Address/City
SupplierCountry Address/Country
(used for $select and $filter)
ContactFirstName FirstName
ContactLastName LastName
(data type: Edm.String)
(data type: Edm.Guid)
(data type: Edm.Date)
(data type: Edm.DateTime)



(ProductID is used for calculation)




(TypeCode is used for calculation)


(Price is used for calculation)


We aren’t listing all those properties from backend service which we’re skipping


Merge data of BusinessPartner, Contact, Product entities

Our intention is to have only one entity which offers a short overview on the data which is interesting for our users.
So we want to have pieces of data of the BusinessPartner, the main Contact and its Products in one entity instead of three entities.
Since these 3 entities are related, they can be reached via navigation properties.
Means, when using the backend service, we can read one BusinessPartner and from there we can navigate via a navigation property to the related products.


Since a BusinessPartner is not meant to be a human, but rather a company, we can navigate to the (human) contact persons via the following URL:


Now, instead of following navigation properties in separate single calls, in OData, we can merge data of related entities by adding the parameter $expand to the URL



When invoking this URL, you might need to be patient, due to possibly huge amount of data.
Remember that it is a public service and everybody is allowed to create additional entries

The service GWSAMPLE_BASIC, which we’re using as backend v2 service, allows to do a reset to the sample data.
It is the function import called “RegenerateAllData”, see here for instructions


Programmatically, the $expand is realized as follows:

ODataQueryResult result = ODataQueryBuilder
		.withEntity("/sap/opu/odata/IWBEP/GWSAMPLE_BASIC", "BusinessPartnerSet")
		.expand("ToContacts", "ToProducts")

In the backend service, a BusinessPartner can have multiple contact persons.
In our v4 service, we use only the first contact which we find.


Reduce amount of data

When comparing the metadata of our exposed model and the consumed model, it becomes obvious that we’re significantly decreasing the amount of information by reducing the number of properties.

See Appendix section for a comparison of the structure of our exposed v4 service and the consumed v2 service.

We’re reducing the amount of data in several aspects:
Less entities, no complex types
Less number of properties
Aggregation of a to-many navigation into a single property


When it comes to reducing the number of properties, it is easy, we did that in the previous blogs:
Fetch the data and pick only what is relevant for us.

Now, there’s a better way:
Why should we fetch data which we actually don’t want?
To avoid network overhead, we should only fetch the data which we need.

That can be achieved with $select.

We’ve covered the $select in terms of exposing/provisioning, however now we need it when calling/consuming the backend service.

Example of how we want to call the backend service:


Moreover, in our call to the backend, we not only want to apply a $select statement to the BusinessPartner, but also to the expanded Contacts and Products
This is possible.
We have to specify the qualified name of expanded property, by prefixing the navigation property name and a slash


In our example:



This is considered in the following full URL$expand=ToProducts,ToContacts&$select=BusinessPartnerID,CompanyName,Address,ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode,ToContacts/FirstName,ToContacts/LastName,ToContacts/ContactGuid,ToContacts/DateOfBirth

Such long URL is hard to read?
Maybe try in following scroll-able formatting:$expand=ToProducts,ToContacts&$select=BusinessPartnerID,CompanyName,Address,ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode,ToContacts/FirstName,ToContacts/LastName,ToContacts/ContactGuid,ToContacts/DateOfBirth

Applying $select to nested properties of a ComplexType is not supported in OData v2


In our code we add the $select statement as follows:

.select("BusinessPartnerID", "CompanyName", "Address", 
   "ToProducts/ProductID", "ToProducts/Price","ToProducts/TypeCode", 
   "ToContacts/FirstName", "ToContacts/LastName", "ToContacts/ContactGuid", "ToContacts/DateOfBirth") 

As usual in the API, it is a list of strings, representing the names of properties.


The above statement doesn’t show one aspect which I’d like to bring to your attention:
In case you have a URL which combines $expand and $select.
In such case, you have to make sure that the navigation properties which you use in your expand statement, must be listed in the $select statement as well.
It is obvious, but easy to forget: the framework can only consider expand-properties if they’re contained in the received portion of data, i.e. if they’re part of the $select.


Expose only Suppliers

In the backend service, the entity BusinessPartner is defined to be generic.
A business partner can be a customer or a supplier. Both have the same properties, so the backend service has been designed to not have to similar entities, but only one.
There’s a flag which indicates if it is a customer or a supplier: the property BusinessPartnerRole
The property is of type Edm.String and the possible values are stored in a separate entity set.

The entity set VH_BPRoleSet is kind of static, it cannot be modified (e.g. CREATE is not allowed), it is a so-called value help

We can invoke the following URL to find out which are the possible values:

and as result, we get 2 entries:
Role 01 with short text “Customer” and role 02 with short text “Supplier”

Like that, we can figure out how to identify the suppliers from the list of BusinessPartners:
We want only those BusinessPartner entities which have value 02 for the property BusinessPartnerRole

In OData, this requirement is realized with a filter statement:

&$filter=BusinessPartnerRole eq ‘02’



The full URL:$filter=BusinessPartnerRole%20eq%20%2702%27&$expand=ToProducts,ToContacts&$select=BusinessPartnerID,CompanyName,Address,ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode,ToContacts/FirstName,ToContacts/LastName,ToContacts/ContactGuid,ToContacts/DateOfBirth


As of OData specification, there’s a blank after the property name and an inverted comma around the value.
However, since in HTTP, the URL is based on ASCII, the special characters have to be encoded. The browsers automatically does hat.
However, we need to be aware of the encoded URL, some REST clients require it. Also, when programmatically specifying URLs. That’s why it is printed above.
(you may find more info here)



Luckily, when using the SAP Cloud Platform SDK for service provisioning, that is handled by the framework.

As follows:



Add some new data

In addition to above tasks, we show that in our OData service implementation we can add some new properties, which aren’t there in the backend service, and we fill those properties with values which are calculated based on backend values.

What we’re doing are just little examples to show what is possible and how easy it is to achieve.

The idea is about products:
For each supplier, we can follow the navigation property to see the list of products belonging to him.
In our above implementation we do it within one call with a $expand statement.
Since it is a one-to-many navigation, the result is a list.
In our v4 service, we want to flatten the list, to make it simpler, e.g. to just have a rough overview.
As such, we take the list and count the number and put the value into a new property called “ProductsTotalAmout”
Furthermore, we take the property “ProductID” from each product in the list, then we concatenate all IDs into one string and put the value into a new property “ProductsToSupply”
Similarly, for each product we check the value of the type code.
What type code?
Background: a product has a property “TypeCode” and the possible values can be inspected via the value help collection


Possible values are AD (Advertisement) or PR (Product)
Our intention is to have in our service a boolean property (ProductsContainAds) indicating if the supplier has advertisements in his list of products.
What is it useful for?
For nothing.
And the last self-calculated property is simple, it sums up the price of all supplied products to a new property ProductsTotalValue

For this task, there’s no additional URL parameter, we only have to make sure that we get the data which we need for our calculations.

Even if we don’t expose them, the relevant properties must be included in the $select statement:

&$select= ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode

We have covered that in the above section.

With respect to implementation, this is pure Java homework.
So no need to give a code snippet here.
These calculations are done in small helper methods, you’re anyways used to find the full java code at the end of this page.


Some more technical aspects

You might have wondered why we have the birthday in our simplified model, which contains only the most relevant data for overview…
The reason:
There’s a difference in the OData specification between v2 and v4
In OData v2, the data type Edm.DateTime was used, which has been removed in v4
See What’s new in v4 (vs v2)
So we need some type mapping, but we don’t need to care much.

As shown in the model chapter above, we’ve specified the birth date as Edm.Date

<Property Name="ContactBirth" Type="Edm.Date" />

Whereas in the v2 service it is Edm.DateTime

<Property Name="DateOfBirth" Type="Edm.DateTime"

In our java code, when we’re doing the mapping, we just pass the value we get from the backend contact into our supplier property

supplierEntity.put("ContactBirth", contactsMap.get("DateOfBirth"));

We don’t need to care about the data type, as long as we’re using HashMaps to store the values.


For those of you using POJOs, the following info should be useful:

The Java type which is used by the framework for the DateOfBirth property is java.util.GregorianCalendar
See documentation

By the way, this is a good example why it makes sense to replace the Edm.DateTime by Edm.Date:
The backend service represents the birthday as Edm.DateTime, which includes the time. However, hours and seconds are just ignored in the backend storage, the time is always zero.
Example payload:



Full Query implementation

Bringing it all together.
Please refer to the Appendix section for the full source code which includes the helper methods

@Query(serviceName = "DemoService", entity = "Suppliers")
public QueryResponse getSuppliers(QueryRequest queryRequest) {
	String[] select = { "BusinessPartnerID", "CompanyName", "Address",   
			"ToProducts/ProductID", "ToProducts/Price", "ToProducts/TypeCode", 
			"ToContacts/FirstName", "ToContacts/LastName", "ToContacts/ContactGuid", "ToContacts/DateOfBirth" }; 
	try {
		List<Map<String, Object>> v2BusinessPartnerList  = ODataQueryBuilder
				.withEntity("/sap/opu/odata/IWBEP/GWSAMPLE_BASIC", "BusinessPartnerSet")
				.expand("ToContacts", "ToProducts")

		// convert the v2 map to v4 map, including all the modifications
		List<Map<String, Object>> v4SupplierList = convertV2BusinessPartnersToV4Suppliers(v2BusinessPartnerList);
		return QueryResponse.setSuccess()
	} catch (ODataException e) {
		return QueryResponse.setError(ErrorResponse.getBuilder()
				.setMessage("Error occurred while calling OData V2 data source.")



After build and deploy, we can run the service and view the Suppliers list


And we can compare the result to the corresponding call to the v2 backend service, including expanded contacts and products$filter=BusinessPartnerRole%20eq%20%2702%27&$expand=ToProducts,ToContacts&$select=BusinessPartnerID,CompanyName,Address,ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode,ToContacts/FirstName,ToContacts/LastName,ToContacts/ContactGuid,ToContacts/DateOfBirth&$format=json




Appendix 1

Source code of manifest file: manifest.yml


  - name: DemoProject
    memory: 512M
    buildpack: sap_java_buildpack
    path: target/DemoProject-0.0.1-SNAPSHOT.war  
    - demoxsuaa
    - demodestination

Appendix 2

Source code of model file: DemoService.xml

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="">
		<Schema Namespace="demo" xmlns="">
			<EntityType Name="Supplier">
				    <PropertyRef Name="SupplierIdentifier"/>
				<Property Name="SupplierIdentifier" Type="Edm.String"/>  
				<Property Name="SupplierCompanyName" Type="Edm.String" />
				<Property Name="SupplierCity" Type="Edm.String"/> 
				<Property Name="SupplierCountry" Type="Edm.String"/> 
				<Property Name="ContactFirstName" Type="Edm.String"/>
				<Property Name="ContactLastName" Type="Edm.String"/>
				<Property Name="ContactGuid" Type="Edm.Guid"/>  
				<Property Name="ContactBirth" Type="Edm.Date" />
				<Property Name="ProductsToSupply" Type="Edm.String"/>
				<Property Name="ProductsTotalAmout" Type="Edm.Int16"/>
				<Property Name="ProductsContainAds" Type="Edm.Boolean"/>
				<Property Name="ProductsTotalValue" Type="Edm.Decimal" Scale="2" Precision="8"/>
			<EntityContainer Name="EntityContainer">
				<EntitySet Name="Suppliers" EntityType="demo.Supplier"/>


Appendix 3

Source code of Java file:

package com.example.odata.DemoProject;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.http.HttpStatus;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class ServiceImplementation {

	Logger logger = LoggerFactory.getLogger(ServiceImplementation.class);

	@Query(serviceName = "DemoService", entity = "Suppliers")
	public QueryResponse getSuppliers(QueryRequest queryRequest) {
		// compose $select statement
		String[] select = { "BusinessPartnerID", "CompanyName", "Address",   
				"ToProducts/ProductID", "ToProducts/Price", "ToProducts/TypeCode", 
				"ToContacts/FirstName", "ToContacts/LastName", "ToContacts/ContactGuid", "ToContacts/DateOfBirth" }; 
		try {
			// call the backend v2 service
			List<Map<String, Object>> v2BusinessPartnerList  = ODataQueryBuilder
					.withEntity("/sap/opu/odata/IWBEP/GWSAMPLE_BASIC", "BusinessPartnerSet")
					.expand("ToContacts", "ToProducts")

			// convert the result (v2) map to v4 map, including all the modifications
			List<Map<String, Object>> v4SupplierList = convertV2BusinessPartnersToV4Suppliers(v2BusinessPartnerList);
			return QueryResponse.setSuccess()
		} catch (ODataException e) {
			return QueryResponse.setError(ErrorResponse
					.getBuilder().setMessage("Error occurred while calling OData V2 data source. "
							+ e.getODataExceptionType() + " - " + e.getMessage())
	private List<Map<String, Object>> convertV2BusinessPartnersToV4Suppliers(List<Map<String, Object>> v2BusinessPartnerListExpanded){
		List<Map<String, Object>> v4supplierList = new ArrayList<Map<String, Object>>();
        if (v2BusinessPartnerListExpanded != null && v2BusinessPartnerListExpanded.size() > 0) {
            for (Map<String, Object> businessPartnerExpanded : v2BusinessPartnerListExpanded) {
                Map<String, Object> supplierEntity = convertV2BusinessPartnerToV4Supplier(businessPartnerExpanded);
		return v4supplierList;
	private  Map<String, Object> convertV2BusinessPartnerToV4Supplier(Map<String, Object> v2BusinessPartnerExpanded) {
    	Map<String, Object> supplierEntity = new HashMap<String, Object>();
        supplierEntity.put("SupplierIdentifier", v2BusinessPartnerExpanded.get("BusinessPartnerID"));
        supplierEntity.put("SupplierCompanyName", v2BusinessPartnerExpanded.get("CompanyName"));
        // Address property is a ComplexType, non-nullable 
        Map<String, Object> v2BusinessPartnerAddress = (Map<String, Object>)v2BusinessPartnerExpanded.get("Address");
     	supplierEntity.put("SupplierCity", v2BusinessPartnerAddress.get("City"));
    	supplierEntity.put("SupplierCountry", v2BusinessPartnerAddress.get("Country"));
    	// ToContacts is an expanded navigation property, here we flatten it into the supplier
		List<Map<String, Object>> contactsMapList = (List<Map<String, Object>>)v2BusinessPartnerExpanded.get("ToContacts");
		if(contactsMapList!= null && ! contactsMapList.isEmpty()) {
			//in v2 backend, one BusinessPartner can have many contacts. Here, for simplification, we take only the first
			Map<String, Object> contactsMap = contactsMapList.get(0);
			supplierEntity.put("ContactGuid", contactsMap.get("ContactGuid"));
			supplierEntity.put("ContactFirstName", contactsMap.get("FirstName"));
			supplierEntity.put("ContactLastName", contactsMap.get("LastName"));
			supplierEntity.put("ContactBirth", contactsMap.get("DateOfBirth"));

    	// ToProducts is an expanded navigation property, here we use it to calculate new fields
		List<Map<String, Object>> productsList = (List<Map<String, Object>>)v2BusinessPartnerExpanded.get("ToProducts");
		if(productsList!= null && ! productsList.isEmpty()) {
			supplierEntity.put("ProductsToSupply", calculateProductsAsString(productsList));
			supplierEntity.put("ProductsTotalAmout", calculateProductsCount(productsList));
			supplierEntity.put("ProductsContainAds", calculateProductsContainAds(productsList));
			supplierEntity.put("ProductsTotalValue", calculateProductsValue(productsList));

		return supplierEntity;
	/** @return the number of product entries */
	private int calculateProductsCount(List<Map<String, Object>> productsList) {
		return productsList.size();
	/** @return true if among the given product list there's an entry of type Advertisement */
	private boolean calculateProductsContainAds(List<Map<String, Object>> productsList) {
	/** @return all product IDs concatenated as one string */
	private String calculateProductsAsString(List<Map<String, Object>> productsList) {
				.collect(Collectors.joining(", ")); // need <String> to compile fine
	/** @return the sum of all products in the given list */
	private BigDecimal calculateProductsValue(List<Map<String, Object>> productsList) {
				.reduce(BigDecimal.ZERO, (lhs, rhs) -> lhs.add(rhs), BigDecimal::add);


Appendix 4

Creating DateOfBirth value

Unfortunately, we usually don’t find entries in the sample data of ES5 system, containing values for DateOfBirth property of the contacts.
Such that we cannot compare the backend value with our v4 service result.

But that’s not a problem, we just need to send an UPDATE to the backend service, to add some value to the entries we want to compare.
However, we cannot just update an arbitrary contact, it should be one which we see in our supplier list.


And this is how it is done:

Example request:


use a guid from your supplier, e.g. ContactSet(guid’0050568c-901d-1ed8-96c7-e9e794e4a0fa’)

HTTP Verb:



(Please refer here for explanation about the csrf token)

x-csrf-token: <your fetched token>
Content-Type:    application/json

Request body:

    "DateOfBirth": "\/Date(990316800000)\/"

(corresponds to 5/20/2001, 2:00:00 AM)


Alternative format:

    "DateOfBirth": "1988-12-22T21:33:11"

If you’re sending this payload to the backend service, make sure to enter any arbitrary data for the time. Remember that the data type is Edm-DateTime, so the time cannot be omitted.

You might find this converter tool useful.




Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Tri Minh Le
      Tri Minh Le

      Hi Carlos Roggan, 

      Very helpful blog. I have one question regarding this line.


      What if I want to filter BusinessPartnerRole dynamically? As I look to the SDK documentation, it hasn't supported $filter operation yet.



      Author's profile photo Carlos Roggan
      Carlos Roggan
      Blog Post Author

      Hi Tri Minh Le ,
      yes, that's a good point, at the end of the day, you'll want to dynamically pass the query options. However, filter is a very complex topic, especially in v4 the spec has been enhanced versus v2.
      Right now, the query object instance doesn't provide access to the filter statement, so currently what you're asking for, is unfortunately not supported.
      Kind Regards and thanks for the feedback 😉


      Author's profile photo Tri Minh Le
      Tri Minh Le

      Hi Carlos Roggan, .

      I'll use your demo to explain my other question :D.

      I have 3 services but they have different URL services.

      For example: Backend v2 entity : Business Partners from /sap/opu/odata/IWBEP/GWSAMPLE_BASIC, Contacts from /sap/opu/odata/IWBEP/GWSAMPLE_BASIC_2, Products from /sap/opu/odata/IWBEP/GWSAMPLE_BASIC_3. 

      When query /odata/v4/DemoService/Suppliers, I want to call 3 services in method public QueryResponse getSuppliers. Is it possible? Does SDK support synchronous or asynchronous call because I may use the output of one service as the input of the other one?



      Author's profile photo Carlos Roggan
      Carlos Roggan
      Blog Post Author

      Hello Tri Minh Le ,

      quick reply: yes


      In your service implementation, you're free to do what you want.
      You can call any other service and afterwards use the result for anything else.
      So why not 3 services in a row, synchronously.
      If any service call fails, then <your service> will just fail as well.
      You can wrap your internal service-calls in Hystrix commands, for better resiliance.

      I think this is not only possible, but also a nice use case, showing the advantage of such development is the flexibility and integration

      Your service might have poor performance at the end, but that's the price to be paid.

      Did you expect any FWK-support for calling multiple backend-services asynchronously?

      Otherwise, other solutions like SAP Cloud Integration, message broker, could be meaningful.

      Kind Regards,


      Author's profile photo Venu Ravipati
      Venu Ravipati

      Hi Carlos,

      I have a basic question regarding this SDK. The below links are giving 403 error. Is this SDK still relevant and supported by SAP?

      are the help links working for you? if i am using wrong links, can you provide the correct link for documentation.


      Creating a Simple OData V4 Service That Exposes Mock Data

      Creating an OData V4 Service from an OData V2 Service

      Creating an OData V4 Service That Exposes a CDS Data Source

      Creating an OData V4 Service That Exposes Data From Multiple Data Sources


      Thank you very much,


      Author's profile photo Carlos Roggan
      Carlos Roggan
      Blog Post Author

      Hello Venu, thanks for pointing out.

      The reason why these pages have been removed/moved is:

      "In the meantime, the Application Programming Model for SAP Cloud Platform has been released"

      I had updated the overview page with this comment.

      The service SDK is still usable, the docu has been moved under the hood of the Application Programming Model.