Technical Articles
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 :
- 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.
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.
Ya, Its not a clean code . I was just scribbling some code , will definitely update post . 🙂
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.
Compare
and
I think we have a clear winner 😉
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. 🙂
Thanks for sharing! In addition to what Tom mentioned, some questions / comments from me: