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

[vinu_Kurian]

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.