Skip to Content

How to implement support for toupper and tolower in $filter


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

Be the first to leave a comment
You must be Logged on to comment or reply to a post.