$top and $skip Query option in SAP OData Service

3
5873

Hello everyone, in this tutorial we will learn how to use query option $top and $skip 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

What $top and $skip query option does ?

Query option $top and $skip are used to restrict the amount of data retrieved from backend 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 $top and $skip query option. $top and $skip 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.

Go to ABAP Workbench

 

 

 

 

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 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 on to the BAPI result data to get the desired data as per the $top and $skip query options.

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 $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′.

$top Query Option

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

$top and $skip Qurey Option

 

Stay tuned to us for more SAP Netweaver Gateway tutorials.

Thank you. Please feel free to comment and let us know your feedback.

  • ys4abap

    Hi..nice article
    i tried to replace the loop, restricting records based on $top & $skip, with a direct delete on internal table
    if lv_skip ge 1.
    delete lt_entityset from 1 to lv_skip.
    delete lt_entityset from ( lv_top + lv_skip ).
    elseif lv_top ge 1.
    delete lt_entityset from ( lv_top + 1 ).
    endif.

  • Georgi

    You can use this instead of the loop:
    IF lv_skip GE 1.
    DELETE et_entityset FROM 1 TO lv_skip.
    ENDIF.
    IF lv_top GE 1.
    DELETE et_entityset FROM ( lv_top + 1 ).
    ENDIF.

  • TeamSAPLearners

    Yes, that is better approach with better performance, we will update the code.
    Thanks for your feedback.

    Thanks,
    Prakash