$top and $skip Query option in SAP OData Service

05/20/2015

33848

Dear SAPLearners, in this tutorial we will learn how to use query option $top and $skip in the SAP OData service.

Before proceeding further we assume that you know how to build an OData service in sap gateway.

Access all SAP Netweaver Gateway tutorials here. Let’s get started

What $top and $skip query option does ?

Query options $top and $skip are used to restrict the amount of data retrieved from the back-end system.

Client-side paging is possible by using this query option $top=n query option will retrieve the top n records from the OData service feed/collection.

$skip=x will go with $top=n query option, it will retrieve top n records by skipping the first x records from the OData service feed/collection.

Supported System Version

SAP NetWeaver Gateway Release 2.0 Support Package >=03

Business Example

A gateway OData service is getting all the products from the back-end system.

You want only top 5/first 5 records from the list of products, you can get that by adding $top=5 to the OData service URI.
You want top 5 records by skipping the first 3 records, you can get that by adding $top=5&$skip=3 to the OData service URI.

Syntax

http://<server>:<port>/sap/opu/odata/sap/<service_name>/ProductsSet?$top=5
http://<server>:<port>/sap/opu/odata/sap/<service_name>/ProductsSet?$top=5&skip=3

How to Implement $top and $skip query option in OData Service

In this section, we will adjust the OData service to respond to the $top and $skip query options.

$top and $skip query options should be applied only to Entity Type sets. We assume that you have already built 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 $top and $skip query option.

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 $top and $skip query option.

$top and $skip query options are accessible in the method by using the below 2 lines of code.

* To get the $top value
lv_top = io_tech_request_context->get_top( ).

* To get the $skip value
lv_skip = io_tech_request_context->get_skip( ).

4. Put the below code in the method PRODUCTSSET_GET_ENTITYSET to respond to the $top and $skip query option.

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,
       ls_entityset TYPE zcl_zdemo_gw_srv_mpc=>ts_products,
       lt_entityset TYPE zcl_zdemo_gw_srv_mpc=>tt_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 ls_entityset.
        APPEND ls_entityset TO lt_entityset.
      ENDLOOP.
    ENDIF.

* $top and $skip Query Options
    DATA: lv_top         TYPE i,
          lv_skip        TYPE i,
          lv_table_size  TYPE i.

    lv_top = io_tech_request_context->get_top( ).
    lv_skip = io_tech_request_context->get_skip( ).

*   >> Client Paging (top/skip)
    IF lv_top IS NOT INITIAL OR
       lv_skip IS NOT INITIAL.
      LOOP AT lt_entityset INTO ls_entityset.
        IF sy-tabix > lv_skip.
          APPEND ls_entityset TO et_entityset.
          lv_table_size = lines( et_entityset ).
          IF lv_top IS NOT INITIAL AND
             lv_table_size >= lv_top.
            EXIT.
          ENDIF.
        ENDIF.
      ENDLOOP.
    ELSE.
*   >> No Paging
      et_entityset = lt_entityset.
    ENDIF.

5. What we did in the above step.

First, we got all the products and then get $top and $skip query options value from the service request.
Next, we applied them to the BAPI result data to get the desired data as per the $top and $skip query options.

6. From a code perspective we are ready, so let’s test the service by using the sap gateway client /IWFND/GW_CLIENT.

7. If we execute the service without adding the $top and $skip query option, we should get all the products available in the system.

If we add the $top=5 query option to the service we should get the top 5 records. Test the service by providing the following URI with $top=5.

/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/ProductsSet?$top=5′.

If we add the $top=5&$skip=2 query option to the service we should get the top 5 records by skipping the first 2 records. Test the service by providing the following URI with $top=5&$skip=2

/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/ProductsSet?$top=5’&skip=2′.