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 :
- Will be having almost 6 parameters (May be more).
- 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.
- 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.
