Experiences with SAP Gateway

"My foots always in my mouth i just can't stomach defeet" – Hilltop Hoods

Filter String How To

leave a comment »

Today I was asked during a demo to share how I use IV_FILTER_STRING and get round the Filter String Gotcha.

To start with the following code is not optimized and should be used for reference purposes only, for example it uses string values, not types and the select should use an index key select first before getting results.

I use a Paging Query Provider (see image below), it has a method SET_FILTER_STR which converts the provided IV_FILTER_STRING into a table of Filter String Select Options, these values are then passed to a method SET_WHERE_FILTER which is used to assign filter select options to one of 10 available range tables.

The range tables are then used to create a WHERE clause for a generic SQL SELECT statement which looks similar to below.

SELECT ( m_fields )
UP TO ( m_up_to )
INTO  TABLE et_results
FROM ( m_table )
WHERE ( m_where_str )
ORDER BY ( m_order_str )

The Code for the filter string conversion looks something like –

* <SIGNATURE>---------------------------------------------------------------------------------------+
 * | Instance Public Method ZCL_GWPAGINGQUERYPROVIDER->ZIF_GWPAGINGQUERY~SET_FILTER_STR
 * +-------------------------------------------------------------------------------------------------+
 * | [--->] IV_FILTER_STRING               TYPE        STRING
 * +--------------------------------------------------------------------------------------</SIGNATURE>
 METHOD zif_gwpagingquery~set_filter_str.

   DATA:
     lt_filter_select_options TYPE /iwbep/t_mgw_select_option,
     lt_filter_string TYPE TABLE OF string,
     lt_key_value TYPE /iwbep/t_mgw_name_value_pair,
     ls_filter_string TYPE string,
     lv_input TYPE string,
     lv_name TYPE string,
     lv_value TYPE string.

   CONSTANTS:
     co_substringof TYPE string VALUE 'substringof(',
     co_startswith TYPE string VALUE 'startswith(',
     co_endswith TYPE string VALUE 'endswith('.

   FIELD-SYMBOLS:
     <fs_range_tab> LIKE LINE OF lt_filter_select_options ,
     <fs_select_option> TYPE /iwbep/s_cod_select_option,
     <fs_key_value> LIKE LINE OF lt_key_value.

   lv_input = iv_filter_string.

 *--- get rid of ) & ' and make AND's uppercase
   REPLACE ALL OCCURRENCES OF ')' IN lv_input WITH ''.
   REPLACE ALL OCCURRENCES OF `'` IN lv_input WITH ''.
   REPLACE ALL OCCURRENCES OF 'and' IN lv_input WITH 'AND'.
   REPLACE ALL OCCURRENCES OF 'eq' IN lv_input WITH 'EQ'.
   SPLIT lv_input AT 'AND' INTO TABLE lt_filter_string.

 *--- build a table of key value pairs based on filter string
   LOOP AT lt_filter_string INTO ls_filter_string.
     APPEND INITIAL LINE TO lt_key_value ASSIGNING <fs_key_value>.

     IF ls_filter_string CS co_substringof.
       ls_filter_string = substring_after( val = ls_filter_string sub = co_substringof ).
       CONDENSE ls_filter_string.
       SPLIT ls_filter_string AT ',' INTO lv_value lv_name.
       <fs_key_value>-value = |*{ lv_value }*|.
     ELSEIF ls_filter_string CS co_startswith.
       ls_filter_string = substring_after( val = ls_filter_string sub = co_startswith ).
       CONDENSE ls_filter_string.
       SPLIT ls_filter_string AT ',' INTO lv_name lv_value.
       <fs_key_value>-value = |{ lv_value }*|.
     ELSEIF ls_filter_string CS co_endswith.
       ls_filter_string = substring_after( val = ls_filter_string sub = co_endswith ).
       CONDENSE ls_filter_string.
       SPLIT ls_filter_string AT ',' INTO lv_name lv_value.
       <fs_key_value>-value = |*{ lv_value }|.
     ELSE.
       CONDENSE ls_filter_string.
       SPLIT ls_filter_string AT ' EQ ' INTO lv_name lv_value.
       <fs_key_value>-value = |{ lv_value }|.
     ENDIF.
     <fs_key_value>-name = to_upper( lv_name ).
    ENDLOOP.

 *--- add key value pairs to filter select options
   LOOP AT lt_key_value ASSIGNING <fs_key_value>.
     APPEND INITIAL LINE TO lt_filter_select_options ASSIGNING <fs_range_tab>.
     <fs_range_tab>-property = <fs_key_value>-name.
     APPEND INITIAL LINE TO <fs_range_tab>-select_options ASSIGNING <fs_select_option>.
     <fs_select_option>-sign = 'I'.
     IF <fs_key_value>-value CS '*'.
       <fs_select_option>-option = 'CP'.
     ELSE.
       <fs_select_option>-option = 'EQ'.
     ENDIF.
     <fs_select_option>-low = <fs_key_value>-value.
   ENDLOOP.
 *--- call method that create where string for filter select options
   me->set_where_filter( lt_filter_select_options ).
 ENDMETHOD.
.
.
 * <SIGNATURE>---------------------------------------------------------------------------------------+
 * | Instance Public Method ZCL_GWPAGINGQUERYPROVIDER->ZIF_GWPAGINGQUERY~SET_WHERE_FILTER
 * +-------------------------------------------------------------------------------------------------+
 * | [--->] IT_FILTER_SELECT_OPTIONS       TYPE        /IWBEP/T_MGW_SELECT_OPTION
 * +--------------------------------------------------------------------------------------</SIGNATURE>
 METHOD zif_gwpagingquery~set_where_filter.

   DATA:
     lv_property TYPE string,
     lv_range_name TYPE string.

   FIELD-SYMBOLS:
     <fs_filter_select_option> LIKE LINE OF it_filter_select_options,
     <fs_range> TYPE /iwbep/t_cod_select_options.

   LOOP AT it_filter_select_options ASSIGNING <fs_filter_select_option>.
     ADD 1 TO m_range_tabix. "Assumption max 10 filters
     lv_range_name = |MRA_RANGE{ m_range_tabix }|.

     ASSIGN (lv_range_name) TO <fs_range>.
     <fs_range> = <fs_filter_select_option>-select_options.

     lv_property = to_upper(<fs_filter_select_option>-property).
     "build where string
     IF m_where_str IS NOT INITIAL.
       m_where_str = |{ m_where_str } AND |.
     ENDIF.

     m_where_str = |{ m_where_str }{ lv_property } IN { lv_range_name }|.

   ENDLOOP.
 ENDMETHOD.

At runtime.

The result.

Advertisements

Written by rsol1

January 25, 2012 at 2:35 pm

Posted in Uncategorized

Tagged with , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: