In this blog we will take a look at recursion for solving OData dynamic where clause.

SAP had already advanced in Gateway service technology and today’s technology (CDS) does need a Gateway service to be built manually.

However, many clients out there are still using manual Gateway services as a primary data source of Fiori or custom UI5 applications.

The challenge of building such OData services starts with the filter and its use in where clause for select. There is no standard way of building where based on entity, except manually writing each property and its select-options in the where for a given query.

This is a cumbersome and repetitive process, also it involves a lot of variables, the more variables we use the more problems they bring along.

The solution to make the development simpler is a dynamic where clause, a method should return required where irrespective of entities or properties based on the “filter select option” table.

This is my way of solving this challenge by using recursion:

Method signature:

  class-methods RECURSION
    importing
      !IV_INDEX type I
      !IT_FILTER_SELECT_OPTIONS type /IWBEP/T_MGW_SELECT_OPTION
    returning
      value(RV_WHERE) type STRING .

Method implementation:

METHOD recursion.

"== Check if we are at the bottom of the table
ASSIGN it_filter_select_options[ iv_index ] TO FIELD-SYMBOL(<lfs_itab>).
CHECK <lfs_itab> IS ASSIGNED.

"== Evalute Inner table for OR conditions, could be one more recursion?
"== Start Key subset in parentheses
rv_where = |( | &&
           "== L_selopt stores itration result
           REDUCE string( INIT  l_selopt = CONV string( '' )
           "== Itrate through all the rows
           FOR   <l_selopt> IN <lfs_itab>-select_options INDEX INTO l_index
           NEXT
           "== Concat Iteration1 && key operator value && cond(OR)
           l_selopt = l_selopt
           && | { <lfs_itab>-property } { <l_selopt>-option } '{ <l_selopt>-low }' |
           && COND #( WHEN l_index LT lines( <lfs_itab>-select_options ) THEN |OR| ELSE `` )
           "== Close with key subset with parentheses
           ) && | )|.

"== Add AND for each key propety, minus last key because we will end up with extra AND in the end
rv_where = rv_where && COND #( WHEN iv_index LT lines( it_filter_select_options ) THEN | AND | ELSE `` ).

"== Itrate thourgh recursion unless we reach at the bottom of the tree
rv_where = rv_where && recursion( iv_index = iv_index + 1
                                  it_filter_select_options = it_filter_select_options ).

ENDMETHOD.

The above solution is built for a single entity without any navigation and also does not consider a full scope of different comparator operators but it gives an idea for building something better.

This is a very simple recursion but very effective and gives a boost to build OData service manually.

If you like this article, feel free to share, tweet, like or follow me for new articles.

• Alert Moderator