Many times Dynamic SQL makes developer life more easy but any way dynamic SQL comes with more security checks, etc.
In ABAP openSQL, the query can be built completely dynamical. Some time back trying similar things in AMDP ( associated with CDS table function). Here is a small example where the TABLE name is passed from the caller to the AMDP procedure and in SQL Script the QUERY can be build and with help of command EXECUTE IMMEDIATE the QUERY can be executed and query result is assigned to a internal table.
Test and the output shows the record- ( all client results ‘not good’).
AMDP Class-Method
CLASS zcl_amdp_dynamic_select DEFINITION PUBLIC FINAL CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb .
CLASS-METHODS: READ
IMPORTING VALUE(IV_TABLENAME) TYPE TABNAME
EXPORTING VALUE(et_data) TYPE SCARR_TAB.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_dynamic_select IMPLEMENTATION.
METHOD READ BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
DECLARE lv_query STRING;
lv_query = ‘SELECT * FROM ‘ || :iv_tablename;
EXECUTE IMMEDIATE lv_query INTO et_data;
ENDMETHOD.
ENDCLASS.
Let’s build the WHERE condition for the client field.
The client field is added as an importing parameter to the method.
Test- The result shows the records of that particular client.
The SESSION_CONTEXT( ) can also be used to determine the current client also-
Test- The result shows the records of that particular client.
SAPCODES 
Here in AMDP u have scarr_tab what if i will pass mara table ??
LikeLike
AMDPs are executed on DB layer. So target table type as per input table may not possible at the moment. But if you pass MARA as input table and then you target table is SCARR_TAB then certainly SQL execution will throw an exception.
If you know possible value of importing table, then you can have multiple exporting parameters for those tables and a dirty IF..ELSE..ENDIF in the AMDP method may help you, such as
lv_query = ‘SELECT * FROM ‘ || :iv_tablename;
IF iv_tablename = ‘MARA’;
EXECUTE IMMEDIATE lv_query INTO et_mara;
elseif iv_tablename = ‘MAKT’;
EXECUTE IMMEDIATE lv_query INTO et_makt;
ENDIF;
The use case shown here in a different perspective-
1- May be in BW the name of the table is different with the same structure.
2- I face challenge in particular use case where-
I have to build CDS views on some price condition table which is used in a particular application.
The CDS views return the data. But the customer can use a different pricing all together with different condition tables.
In such a case the CDS view won’t consider those tables. To cater this problem have to build a CDS view on top of a CDS table function and that CDS table function AMDP method dynamic derive from which all condition tables it has to get the data.
The condition table structure involves few fixed fields and few other fields specific to that table.
So the dynamic select works fine there as we need to select the fixed fields from these tables.
So the query is not like as SELECT * but SELECT COL1 COL2 and the output table structure is fixed.
Don’t start comparing what we can do on the ABAP AS, such all things in not possible on DB layer.
But yeah for particular use case – we can find what way it works.
Hope this helps to put some insights!
LikeLike
Hi
I am getting error as type SCARR_TAB unknown
LikeLike