Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
Showing results for 
Search instead for 
Did you mean: 
Product and Topic Expert
Product and Topic Expert


Today I got a question whether the function toupper( ) is supported with $filter in SAP Gateway.

The answer is no, but you can handle the filter tree yourself in the GET_ENTIYSET method of your data provider extension class.

This wouldn't help in the concrete context of the question that I got because here toupper( ) should be used with an OData Service that was published via the Annotation @OData.publish : true.

But the Referenced Data Source approach would help here because we can handle the toupper( ) call in the data provider extension class.

If the Statement doesn't match our requirements we will call the get_entityset method in the super class which is handled by the SADL Framework.

However if you try to use toupper( ) for another property you will stll get an error message.

Solution 1: Referenced data source to the rescue

For this blog I created a Service Builder project ZGW_TOUPPER based on the CDS view SEPM_I_Product_E using Referenced Data Source (RDS) with just one entity type SEPM_I_Product_EType.

The following coding only supports $filter statements like the following:

/sap/opu/odata/SAP/ZGW_TOUPPER_SRV/SEPM_I_Product_E?$filter=toupper(ProductCategory) eq 'SPEAKERS'&$format=json

The Response would look like the following excerpt of a JSON response:
"d" : {
"results" : [
"__metadata" : {
"id" : "'')",
"uri" : "'')",
"type" : "ZGW_TOUPPER_SRV.SEPM_I_Product_EType"
"ID" : "",
"Product" : "HT-1090",
"Width" : "12.000",
"Depth" : "10.000",
"DimensionUnit" : "CM",
"ProductPictureURL" : "/sap/public/bc/NWDEMO_MODEL/IMAGES/HT-1090.jpg",
"ProductValueAddedTax" : 1,
"Supplier" : "100000044",
"ProductBaseUnit" : "EA",
"Weight" : "3.000",
"WeightUnit" : "KG",
"Product_Text" : "",
"ProductType" : "PR",
"ProductCategory" : "Speakers",
"CreationDateTime" : "\/Date(1487150757000+0000)\/",
"LastChangedDateTime" : "\/Date(1487150757000+0000)\/",
"Price" : "39.00",
"Currency" : "EUR",
"Height" : "16.000"

The GET_ENTITYSET method producttypeset_get_entityset retrieves the filter tree io_tech_request_context->get_filter_expression_tree( ).

It checks whether the function being used is actually toupper( ) and for simplicity we only support one property.

It then retrieves the filter string and we are using a new option available only as of 751 to use toupper in the WHERE clause of a OpenSQL Statement.
SELECT * FROM sepm_i_product_e WHERE upper( productcategory ) = @lv_literal

If this prerequisites are not met we are calling the get_entityset method of the super calls where the SADL framework will handle our request.

A nice blog about case insensitive search by my colleague horst.keller can be found here:

ABAP News for Release 7.51 – Case Insensitive Search in SQL and Other New Functions

But as said, if you would use toupper( ) for another property an error message would be raised.

Solution 2: CDS view development

Since the described use case was to use an OData Service that uses a CDS view as a data source a much easier approach would be to create a new CDS view with a new column upper_text that performs the conversion into upper text on data base Level.

This way you could either use OData.publish : true or Referenced Data Source without the need to write any specific code in the DPC_EXT class.
upper(text) as upper_text

This option does however require SAP AS ABAP 751 as described in  the aforementioned blog from horst.keller.

Source code

METHOD producttypeset_get_entityset.

DATA : lo_filter_tree TYPE REF TO /iwbep/if_mgw_expr_node,
lo_left_node TYPE REF TO /iwbep/if_mgw_expr_node,
lo_right_node TYPE REF TO /iwbep/if_mgw_expr_node,
lo_binary TYPE REF TO /iwbep/if_mgw_expr_binary,
lo_function TYPE REF TO /iwbep/if_mgw_expr_function,
lo_property TYPE REF TO /iwbep/if_mgw_expr_property,
lo_literal TYPE REF TO /iwbep/if_mgw_expr_literal,
lt_param_tab TYPE /iwbep/if_mgw_expr_function=>parameter_t,
lv_operator TYPE string,
lv_function TYPE string,
lv_literal TYPE string,
lv_property TYPE string,
lv_supported_filter_string TYPE string,
lv_filter_error TYPE string,
lv_wrong_filter TYPE abap_bool.

CONSTANTS : lc_kind_binary TYPE c LENGTH 1 VALUE 'B',
lc_kind_literal TYPE c LENGTH 1 VALUE 'C',
lc_kind_function TYPE c LENGTH 1 VALUE 'F',
lc_kind_property TYPE c LENGTH 1 VALUE 'P'.

lo_filter_tree = io_tech_request_context->get_filter_expression_tree( ).

IF lo_filter_tree IS BOUND.
IF lo_filter_tree->kind = lc_kind_binary. " 'B'

lo_filter_tree->prepare_converted_values( ).
lo_binary ?= lo_filter_tree.
lv_operator = lo_binary->operator.
lo_left_node = lo_binary->left_operand.
lo_right_node = lo_binary->right_operand.

IF lo_left_node IS BOUND.
IF lo_left_node->kind = lc_kind_function. " 'F' .

lo_function ?= lo_left_node.
lv_function = lo_function->function.
IF lv_function <> 'toupper'.
lv_filter_error = 'Only touppper is supported. '.
lv_wrong_filter = abap_true.

lt_param_tab = lo_function->parameters.

IF lt_param_tab IS NOT INITIAL.

IF lt_param_tab[ 1 ]->kind = lc_kind_property.
lo_property ?= lt_param_tab[ 1 ].
lv_property = lo_property->property_name.
lv_wrong_filter = abap_true.

IF lv_property = 'PRODUCTCATEGORY'.

" raise error message that filter string does not match the expected format
" an additional property was found in the filter string
lv_filter_error = 'Property:' && lv_property && ' is not supported. '.
lv_wrong_filter = abap_true.
lv_wrong_filter = abap_true.

lv_wrong_filter = abap_true.

IF lo_right_node IS BOUND.
IF lo_right_node->kind = lc_kind_literal. " 'F' .

lo_literal ?= lo_right_node.

lv_literal = lo_literal->literal_converted.

lv_wrong_filter = abap_true.

lv_wrong_filter = abap_true.

IF lv_wrong_filter = abap_true.

RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
textid = /iwbep/cx_mgw_busi_exception=>business_error_unlimited
message_unlimited = lv_filter_error && lv_supported_filter_string.


SELECT * FROM sepm_i_product_e WHERE upper( productcategory ) = @lv_literal

1 Comment