Skip to Content

How to implement support for toupper and tolower in $filter

Introduction

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" : "http://vegtwy1mst.wdf.sap.corp:50008/sap/opu/odata/SAP/ZGW_TOUPPER_SRV/SEPM_I_Product_E('')",
          "uri" : "http://vegtwy1mst.wdf.sap.corp:50008/sap/opu/odata/SAP/ZGW_TOUPPER_SRV/SEPM_I_Product_E('')",
          "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
INTO CORRESPONDING FIELDS OF TABLE @et_entityset.

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.
            ENDIF.

            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.
              ELSE.
                lv_wrong_filter = abap_true.
              ENDIF.

              IF lv_property = 'PRODUCTCATEGORY'.


              ELSE.
                " 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.
              ENDIF.
            ELSE.
              lv_wrong_filter = abap_true.
            ENDIF.



          ELSE.
            lv_wrong_filter = abap_true.
          ENDIF.
        ENDIF.

        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.

          ELSE.
            lv_wrong_filter = abap_true.
          ENDIF.
        ENDIF.


      ELSE.
        lv_wrong_filter = abap_true.
      ENDIF.
    ENDIF.



    IF lv_wrong_filter = abap_true.

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

    ENDIF.

    SELECT * FROM sepm_i_product_e WHERE upper( productcategory ) = @lv_literal
        INTO CORRESPONDING FIELDS OF TABLE @et_entityset.

  ENDMETHOD.
1 Comment
You must be Logged on to comment or reply to a post.
  • Hi Andre,

    I just wanted to share my opinion here on the proposed solutions. I had been searching around for the right approach to handle case-sensitive filters and was not able to get a feasible and optimised way.

    If my back-end system is HANA DB and I want to leverage my OData service enablement based on CDS Views(either via Reference Data Source or OData Publish = true), then ideally SAP framework should be able to correctly parse my requested URI(ToUpper or ToLower or no case conversion) and handle the filter on its own,The entity might have many such search fields and handling them explicitly in DPC_EXT or creating additional upper case fields would not be feasible.

    Example : <…ServiceURL>/ZC_RES_SUBLOB?$filter=toupper(LOBDescription) eq ‘WATCH’&$format=json

    If I debug the GET calls we see the SQL query is directed to the class CL_SQL_STATEMENT method EXECUTE_QUERY with the generated SQL Statement. The logic of generating this SQL statement, for HANA DB, is in the local class  LCL_EXECUTOR_HDB implementation.

    So we would like the SAP framework to convert the below generated SQL to appropriate HANA functions UPPER or LOWER based on the request.

    This way we completely rely on the standard framework to do the needful, rather than adding custom code or additional non-business related fields in the service.

    Thanks & Regards,

    Samson

    /