Dynamic Select Query (for Custom Entities) – SAP Business Technology Platform (Cloud Platform)

A few days back , I came across a scenario which required a dynamic where condition for a select query to return data via a Custom Entity  ( Please read more about Custom Entities here ->  https://blogs.sap.com/2020/07/03/custom-entities-sap-cloud-platform/ ).

The scenario was like :

1.  Will be having almost 6 parameters (May be more).
2.  If the parameter is empty , never add the parameter ( If parameter is empty it would result in where <field-name> = null value  , adding a null value in selection criteria will return wrong result set   ) to the where condition.
3. No guarantee that a parameter would have value at specific time .

So I decided to go with something similar to classic ABAP dynamic select Query.

Data Definition For custom Entity : 

@ObjectModel.query.implementedBy  : 'ABAP:ZCL_CUSTOMENTITY'
define custom entity ZCustomEntity
  with parameters
    param1 : abap.char(10),
    param2 : abap.char(10),
    param3 : abap.char(10),
    param4 : abap.char(10),
    param5 : abap.char(10),
    param6 : abap.char(10)
{
  key id   : abap.char(10); // Returning fields are mentioned between {} just like ordinary CDS
      name : abap.char(100);

}

Implementation / Class : 

class ZCL_CUSTOMENTITY definition
  public
  final
  create public .

  public section.
    interfaces  IF_RAP_QUERY_PROVIDER .
  protected section.
  private section.
endclass.



class ZCL_CUSTOMENTITY implementation.


  method IF_RAP_QUERY_PROVIDER~SELECT.
    data:IT_RESULT   type  table of ZCUSTOMENTITY. "internal table to be returned
    data: LV_PARAM1    type STRING, "Local variables to fetch and save parameter value
          LV_PARAM2    type STRING,
          LV_PARAM3    type STRING,
          LV_PARAM4    type STRING,
          LV_PARAM5    type STRING,
          LV_PARAM6    type STRING,
          QUERY_STRING type STRING.
    try.
        try.
            if IO_REQUEST->IS_DATA_REQUESTED( ). "standard method to get incoming data
              IO_REQUEST->GET_PAGING( ).

              data(LT_FILTER_COND) = IO_REQUEST->GET_PARAMETERS( ). "setting the filter condition, fetching parameter names from data definition

              LV_PARAM1 = value #( LT_FILTER_COND[ PARAMETER_NAME   = 'param1' ]-VALUE optional ). "fetching the parameter value
              if LV_PARAM1 is not initial.
                concatenate   'field name ' '=' LV_PARAM1  into QUERY_STRING separated by SPACE.
              endif.
              LV_PARAM2 = value #( LT_FILTER_COND[ PARAMETER_NAME   = 'param2' ]-VALUE optional ). "fetching the parameter value
              if LV_PARAM2 is not initial.
                if QUERY_STRING is not initial or QUERY_STRING <> ' ' .
                  concatenate QUERY_STRING 'and field name ' '=' LV_PARAM2  into QUERY_STRING separated by SPACE.
                else.
                  concatenate   'field name ' '=' LV_PARAM2  into QUERY_STRING separated by SPACE.
                endif.
              endif.
              LV_PARAM3 = value #( LT_FILTER_COND[ PARAMETER_NAME   = 'param3' ]-VALUE optional ). "fetching the parameter value
              if LV_PARAM3 is not initial.
                if QUERY_STRING is not initial or QUERY_STRING <> ' ' .
                  concatenate QUERY_STRING 'and field name ' '=' LV_PARAM3  into QUERY_STRING separated by SPACE.
                else.
                  concatenate   'field name ' '=' LV_PARAM3  into QUERY_STRING separated by SPACE.
                endif.
              endif.
              LV_PARAM4 = value #( LT_FILTER_COND[ PARAMETER_NAME   = 'param4' ]-VALUE optional ). "fetching the parameter value
              if LV_PARAM4 is not initial.
                if QUERY_STRING is not initial or QUERY_STRING <> ' ' .
                  concatenate QUERY_STRING ' and field name ' '=' LV_PARAM4  into QUERY_STRING separated by SPACE.
                else.
                  concatenate   'field name ' '=' LV_PARAM4  into QUERY_STRING separated by SPACE.
                endif.
              endif.
              LV_PARAM5 = value #( LT_FILTER_COND[ PARAMETER_NAME   = 'param5' ]-VALUE optional ). "fetching the parameter value
              if LV_PARAM5 is not initial.
                if QUERY_STRING is not initial or QUERY_STRING <> ' ' .
                  concatenate QUERY_STRING ' and field name ' '=' LV_PARAM5  into QUERY_STRING separated by SPACE.
                else.
                  concatenate   'field name ' '=' LV_PARAM5  into QUERY_STRING separated by SPACE.
                endif.
              endif.
              LV_PARAM6 = value #( LT_FILTER_COND[ PARAMETER_NAME   = 'param6' ]-VALUE optional ). "fetching the parameter value
              if LV_PARAM6 is not initial.
                if QUERY_STRING is not initial or QUERY_STRING <> ' ' .
                  concatenate QUERY_STRING 'and field name ' '=' LV_PARAM6  into QUERY_STRING separated by SPACE.
                else.
                  concatenate   'field name ' '=' LV_PARAM6  into QUERY_STRING separated by SPACE.
                endif.
              endif.
              "Using the parameter we could do whatever we want , like selecting from a table , doing certain calculations etc
              try.
                  select * from ZEMPLOYEE where (QUERY_STRING)  into table @IT_RESULT   . " Dynamic Query -- query string contains our fields and their respective  values
                catch CX_SY_DYNAMIC_OSQL_SYNTAX into data(LX_DATA). " exception to catch and manage dynamic query errors . must use this in order to work properly
              endtry.
              IO_RESPONSE->SET_TOTAL_NUMBER_OF_RECORDS( LINES( IT_RESULT  ) ). "setting the total number of records which will be sent
              IO_RESPONSE->SET_DATA( IT_RESULT  ). "returning the data as internal table
            endif.
          catch  CX_RAP_QUERY_PROVIDER into data(LX_EXC).

        endtry.
      catch CX_RFC_DEST_PROVIDER_ERROR into data(LX_DEST).
    endtry.
  endmethod.
endclass.

This piece of information is shared based on my knowledge and experience on BTP , any correction to the above information is accepted through comments . Lets learn together , grow together.