Hello everyone, in this tutorial we will learn how to use query option $orderby in SAP netweaver gateway OData service. Before proceeding further we assume that you know how to build OData service in sap gateway. Access all SAP Netweaver Gateway tutorials here.Lets get started
1.What $orderby query option does ?
Query option $orderby adds sorting capabilities to the SAP gateway OData service. So that you can able to order the OData service feed/collection based on the fields available in the Entity Type set.
2.Supported System Version
SAP NetWeaver Gateway Release-采购向厂商下P/O之后，必须透过一定权限经由主管对此张P/O做 release后才能收料 2.0 Support Package >=03
3.Business Example
A gateway OData service is getting all the products from the back-end system, but you don’t want to see the products in the order they retrieved and you want to apply orderby on the OData service so that you get required products in required order. For example you want to order the all products by its Price in descending.
4.Syntax
http://<server>:<port>/sap/opu/odata/sap/<service_name>/ProductsSet?$orderby=Price desc
$orderby = <fieldname> <sortorder> , where:
<fieldname> is the name of the field.
<sortorder> desc/asc
5.How to Implement $orderby query option in OData Service
In this section we will adjust the OData service to respond to $orderby query option. $orderby query option should be applied only to Entity Type sets. We assume that you have already build the OData service to get the list of products. if you have not click here.
1. After successful creation of OData service. Go back to service builder SEGW, Navigate to the ABAP workbench for the Product Entity set.
2. Code inside the method PRODUCTSSET_GET_ENTITYSET will look like below i.e before adjusting the service to $orderby query option.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DATA: lt_products TYPE STANDARD TABLE OF bapi_epm_product_header, ls_products TYPE bapi_epm_product_header, es_entityset TYPE zcl_zdemo_gw_srv_mpc=>ts_products, lv_max_rows TYPE bapi_epm_max_rows. lv_max_rows-bapimaxrow = 10. CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST' EXPORTING max_rows = lv_max_rows TABLES headerdata = lt_products. IF lt_products IS NOT INITIAL. LOOP AT lt_products INTO ls_products. MOVE-CORRESPONDING ls_products TO es_entityset. APPEND es_entityset TO et_entityset. ENDLOOP. ENDIF. |
3. In the above code snippet, we have retrieved the list of products generally by providing the max rows.Now we will adjust the code to adapt it to the $orderby query option. Orderby query option is available in the method by accessing the importing parameter IT_ORDER.
4. Put the below code in the method PRODUCTSSET_GET_ENTITYSET to respond to the $orderby query option.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DATA: ls_order TYPE /iwbep/s_mgw_sorting_order, lt_products TYPE STANDARD TABLE OF bapi_epm_product_header, ls_products TYPE bapi_epm_product_header, es_entityset TYPE zcl_zdemo_gw_srv_mpc=>ts_products, lv_max_rows TYPE bapi_epm_max_rows. lv_max_rows-bapimaxrow = 10. CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST' EXPORTING max_rows = lv_max_rows TABLES headerdata = lt_products. IF lt_products IS NOT INITIAL. LOOP AT lt_products INTO ls_products. MOVE-CORRESPONDING ls_products TO es_entityset. APPEND es_entityset TO et_entityset. ENDLOOP. ENDIF.. * Get the Order by field name READ TABLE it_order INTO ls_order WITH KEY property = 'Price'. IF sy-subrc = 0. CASE ls_order-order. WHEN 'asc'. SORT et_entityset BY price ASCENDING. WHEN 'desc'. SORT et_entityset BY price DESCENDING. WHEN OTHERS. ENDCASE. ENDIF. |
5. What we did in the above step.First we got all the products and get orderby fieldname, sortorder and pass apply them on to the BAPI result data to get the required sort order of the data.
6. From code perspective we are ready, so lets test the service by using the sap gateway client /IWFND/GW_CLIENT.
7. If we execute the service without adding $orderby query option, then we will get all the products available in the system. If we add the $orderby query option to the service our service will respond to that and it will give the results based on the sort order provided. Test the service by providing the following URI with $orderby=Price desc.
/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/ProductsSet?$orderby=Price desc’.
8. Check the collection/feed, now you should get the products in descinding order of the Price . You can add N number fields with $orderby query option.
Stay tuned to us for more SAP Netweaver Gateway tutorials. Please feel free to comment and let us know your feedback.
In step-4 i statically declaring the sort field name and order, you can also do it more dynamically in this way. Replace the code in step-4 with the below code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
DATA: ls_order TYPE /iwbep/s_mgw_sorting_order, lt_products TYPE STANDARD TABLE OF bapi_epm_product_header, ls_products TYPE bapi_epm_product_header, es_entityset TYPE zcl_zdemo_gw_srv_mpc=>ts_products, lv_max_rows TYPE bapi_epm_max_rows. lv_max_rows-bapimaxrow = 10. CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST' EXPORTING max_rows = lv_max_rows TABLES headerdata = lt_products. IF lt_products IS NOT INITIAL. LOOP AT lt_products INTO ls_products. MOVE-CORRESPONDING ls_products TO es_entityset. APPEND es_entityset TO et_entityset. ENDLOOP. ENDIF.. * Get the Order by field name DATA: lt_tech_order TYPE /iwbep/t_mgw_tech_order, ls_tech_order TYPE /iwbep/s_mgw_tech_order, otab TYPE abap_sortorder_tab, oline TYPE abap_sortorder. lt_tech_order = io_tech_request_context->get_orderby( ). LOOP AT lt_tech_order INTO ls_tech_order. oline-name = ls_tech_order-property. IF ls_tech_order-order = 'desc'. oline-descending = 'X'. ELSE. oline-descending = space. ENDIF. APPEND oline TO otab. ENDLOOP. SORT et_entityset BY (otab). |
