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.
