Netweaver Gateway Development

how to implement $filter query option in SAP OData service?

            $filter query option is the most used query option whil accessing OData service.The framework does not provide default filtering.We need to implement it ourself.$filter query option allows us to filter the result set of GET_ENTITYSET implementation.
          In this tutorial we will use GET_ENTITYSET method from our earlier tutorial mentioned above.


Steps


  1. Open our project and expand service implementation node and right click on GetEntitySet(Query) and click on Goto ABAP Workbench.
  2. Open our FLIGHTSET_GET_ENTITYSET method.Inside this method paste below code.Our filter query passed from service call is inside import paramete it_filter_select_options .We will loop at this table and fetch the filter parameter into a table of structure SELOPT(SELECT-OPTIONS IN ABAP).
  3.     DATA:ls_filter_select_options TYPE /iwbep/s_mgw_select_option,
             ls_select_option         TYPE /iwbep/s_cod_select_option.
        DATA:ls_selectparams TYPE selopt,
             lt_selectparams TYPE STANDARD TABLE OF selopt.
        LOOP AT it_filter_select_options INTO ls_filter_select_options.
          IF ls_filter_select_options-property EQ 'Carrid'.
            LOOP AT ls_filter_select_options-select_options INTO ls_select_option.
              ls_selectparams-sign = ls_select_option-sign.
              ls_selectparams-option = ls_select_option-option.
              ls_selectparams-low = ls_select_option-low.
              ls_selectparams-high = ls_select_option-high.
              APPEND ls_selectparams TO lt_selectparams.
            ENDLOOP.
    
          ENDIF.
    
        ENDLOOP.
    


  4. Fetch data based on the filter query.
  5.       SELECT * FROM SCARR INTO CORRESPONDING FIELDS OF TABLE ET_ENTITYSET
          WHERE Carrid in lt_selectparams.
    
  6. Now we can test our filter query in gateway client( /IWFND/GW_CLIENT).Our service url look like this.http://<host>:<port>/sap/opu/odata/SAP/ZTEST_ODATA_SRV_01/FlightSet?$filter=Carrid eq 'AC'.Press execute button.
Note:Some times filter query can become complex.With abap SELECT-OPTIONS structure we cannot cover all scenarios like  a filter such as FieldA eq ‘A’ or FieldB eq ‘B’ can’t be put into our table because of OR in the filter.In suchcases we can use import parameter IV_FILTER_STRING .Then our select statement should change like this.
          SELECT * FROM SCARR INTO CORRESPONDING FIELDS OF TABLE ET_ENTITYSET
          WHERE (IV_FILTER_STRING).
    
If you enjoyed this post, Please Share!!

15 comments :

  1. Very nice tutorial..
    Can you please elaborate more on applying filter for multiple columns? I did not get what this query does..
    SELECT * FROM SCARR INTO CORRESPONDING FIELDS OF TABLE ET_ENTITYSET
    WHERE (IV_FILTER_STRING).

    Can you please pick an example and explain similar to this blog..

    ReplyDelete
    Replies
    1. Hi,
      IV_FILTER_STRING will have the search string as it is at run time.You can check this by putting an external break point and find the difference.
      For multiple columns you can try the same.just check what it_filter_select_options holds?.For this use this url
      http://:/sap/opu/odata/SAP/ZTEST_ODATA_SRV_01/FlightSet?$filter=Carrid eq 'AC' and Carrname eq 'yourValue'

      Delete
    2. HI , can you please take a sample for this IV_FILTER_STRING . It seem to be different than singe filter operation.
      How can I convert IV_FILTER_STRING to it_filter_select_options ?

      Delete
    3. Hi Hoang Thai,
      You can put an external break-point in segw and check the variable yourself.

      Delete
  2. How to pass this url filter values from sapui5 ?

    ReplyDelete
  3. Hi my odata service url is as mentioned below, how can i pass these parameter values from sapui5 app ?

    /sap/opu/odata/sap/ZREPOSITORY_SRV_07/REP_SEARCHSet?$filter=IStartindex eq '1' and IEndindex eq '2' and ( Column eq 'CLASS' or Column eq 'REFERENCE_NO' ) and ( Operator eq 'CONTAINING' or Operator eq 'CONTAINING' ) and ( Value eq 'BEARING' or Value eq '6202' )

    ReplyDelete
    Replies
    1. You can pass using 'filters' parameter..

      oModel.read("/REP_SEARCHSet", {
      filters: [ /* your filters here */ ],
      success: function(oData) {
      //success
      }
      });

      Delete
  4. I just found out that the static methods of the class /iwbep/cl_mgw_data_util will do sorting, filtering and paging for us.

    ReplyDelete
  5. nice i used this in my program

    ReplyDelete
  6. how to perform delete operation using filter

    ReplyDelete
    Replies
    1. Best Practice is to use delete operation for deletion. Filter is used for refining the result. IF required you can get the values through filter query and write a delete query with those values. Not recommended.

      Delete
  7. How can I convert IV_FILTER_STRING to it_filter_select_options ?

    ReplyDelete
    Replies
    1. IV_FILTER_STRING is of type string and the other is a structure. It's not inter convertible. You can use custom logic to convert it.(Use string operations and populate the it_filter_select_options table using for loop).

      Delete

Powered by Blogger.