Skip to Content
Technical Articles
Author's profile photo Vinu Kurian

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.

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Tom Demuyt
      Tom Demuyt

      There is so much copy pasting here..

      You need a method that appends to query_String with 6 calls, the code would be so much cleaner.

      You should also consider string templates, concatenate is late nineties.

      Author's profile photo Vinu Kurian
      Vinu Kurian
      Blog Post Author

      Ya, Its not a clean code . I was just scribbling some code , will definitely update post . 🙂

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      I'm with you on the 6 calls but you might have missed that in the code it's CONCATENATE... SEPARATED BY. (For some reason SCN blog editor doesn't wrap the code, so some part of it just goes off screen and is difficult to see.)

      I like string templates as much as the next person but in this specific case when we need to have a bunch of variables put together with a separator I'd argue if CONCATENATE... SEPARATED... is actually more readable and efficient since you put the separator just once and it's easy to change. So it's not as much about the decade but about the right tool for the job IMHO.

      Author's profile photo Tom Demuyt
      Tom Demuyt

      Compare

      concatenate 'field name ' '=' LV_PARAM6 into QUERY_STRING separated by SPACE.

      and

      QUERY_STRING = |field name = { LV_PARAM6 }|.

       

      I think we have a clear winner 😉

       

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      If instead of "field name = something" you take 10 random variables that need to be "glued" together with a separator and then someone tells you "oh, by the way, we need to use a comma as a separator" then the winner might be different. 🙂

      In the second example it's actually not that apparent that there is a separator. Unless someone is fluent in reading this syntax, it's hard to tell spaces separating parts of the string template from spaces that are actually part of the result.

      But it's not the hill I'd die on. 🙂

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Thanks for sharing! In addition to what Tom mentioned, some questions / comments from me:

      1. I'm not sure what does this have to do with BTP... It looks like this is using RAP model but that's not a BTP service, as far as I know.
      2. I saw the same thing in the code example in the other blog, so just pointing out that we don't need one TRY... CATCH per exception, we can capture multiple exceptions with the same TRY block.
      3. Is there a specific reason the fields have 10 characters in the data definition? The respective fields are defined as string in the code.
      4. Is there a specific business case for this? It looks suspiciously like reinventing SE16 but without any security measures. 🙂
      Author's profile photo Vinu Kurian
      Vinu Kurian
      Blog Post Author
      1. It was actually written on an ABAP instance running on BTP (Cloud Foundry).
      2. ,3. , 4. Its never a clean code , its just a midnight scribbling , will definitely update the blog with much more cleaner code.