Dynamic Where Condition usage in Database queries
SQL Tips: Dynamic WHERE Condition in Database Queries Let’s begin by discussing how to use dynamic where conditions in database queries. We desire Dynamic Where Condition for data set (DB) inquiries, but there aren’t many changes. Importance: Your software may determine during runtime which fields in the database table should be used for the DB query. To support such Dynamic Where Conditions, we can write our own unique code. In any case, we are aware of the effort and suffering required to write such a code. Thankfully, we can fulfill the requirement by using the Standard SAP capability module CRS_CREATE_WHERE_CONDITION. Using dynamic where clauses in database queries
Test Model:
We should think about it because we have a program that can comprehend part of the reasoning. Finally, it concludes that, in light of the fields MATNR (Material Number) and MTART (Material Sort), it should do an inquiry on the Data Set table MARA. The question can be asked in a few other possible fields, and the selection will be decided at runtime. Until run time, the table fields and their comparison values are unknown. Therefore, a dynamic where condition is what we truly want for the data set inquiry. Under such circumstances, you may choose to look at every possible combination of fields that could be used in the where condition and compose your custom code to produce a Dynamic Where Condition or you can simply utilize a Capability Module (FM) which will finish the work you.
You can also read for:- list of data types in sap abap
We will examine what is going on thinking about a program, with a determination screen having both the fields MATNR and MTART. (Allow us to accept these two fields and their comparing values won’t be accessible until runtime). If it’s not too much trouble, note, this program is composed to make sense of the Dynamic Where Condition. In real venture, you wouldn’t have the boundaries in determination screen. You may very well know the table and field name and values in those fields would be populated at runtime.
For straightforwardness, we have made these fields as a component of determination screen to populate our Dynamic Where Condition.
* Types TYPES: BEGIN OF x_mara, matnr TYPE matnr, "Material Number mtart TYPE mtart, "Material Type END OF x_mara. DATA: * Internal table l_i_range TYPE STANDARD TABLE OF crmselstr, l_i_output TYPE STANDARD TABLE OF mcondition, l_i_mara TYPE STANDARD TABLE OF x_mara, * Work area l_wa_range TYPE crmselstr, * Variable v_matnr TYPE mara-matnr, v_mtart TYPE mara-mtart. * Selection Screen SELECT-OPTIONS: s_matnr FOR v_matnr, s_mtart FOR v_mtart. * For Select Option 1 LOOP AT s_matnr. "Looping to get multiple (single) values l_wa_range-table = 'MARA'. "Name of the DB table l_wa_range-field = 'MATNR'. "Field name the user has selected l_wa_range-sign = s_matnr-sign. "Sign l_wa_range-option = s_matnr-option."option l_wa_range-low = s_matnr-low. "Lower Value l_wa_range-high = s_matnr-high. "Higher Value APPEND l_wa_range TO l_i_range. ENDLOOP.. * For Select Option 2 LOOP AT s_mtart. "Looping to get multiple (single) values CLEAR l_wa_range. l_wa_range-table = 'MARA'. "Name of the DB table l_wa_range-field = 'MTART'. "Field name the user has selected l_wa_range-sign = s_mtart-sign. "Sign l_wa_range-option = s_mtart-option. "option l_wa_range-low = s_mtart-low. "Lower Value l_wa_range-high = s_mtart-high. "Higher Value APPEND l_wa_range TO l_i_range. ENDLOOP. IF NOT l_i_range[] IS INITIAL. * Call the FM to create the Dynamic Where condition CALL FUNCTION 'CRS_CREATE_WHERE_CONDITION' TABLES ti_range = l_i_range to_cond = l_i_output EXCEPTIONS invalid_input = 1 OTHERS = 2. IF sy-subrc = 0. * Special way to write the query SELECT matnr "Material Number mtart "Material Type FROM mara INTO TABLE l_i_mara WHERE (l_i_output). IF sy-subrc = 0. ENDIF. ENDIF. "IF sy-subrc = 0: SELECT matnr mtart ENDIF. "IF NOT l_i_range[] IS INITIAL.
Please note above: The Dynamic Where Condition table has to be put in brackets during the SELECT statement
WHERE (l_i_output).
Let us check the values in debug mode.
In choice screen we gave two qualities to material number field and one worth to material sort.
Check the range internal table l_i_range has data from two fields.
Function module CRS_CREATE_WHERE_CONDITION smartly converts the data to Dynamic Where Condition in internal table l_i_output. Isn’t this cool?
Why would it be wise for us to set aside time to write code to put up our own Dynamic Where Condition at the moment when SAP has provided this FM? You now know which FM to contact the next time you need a Dynamic Where Proviso..
Much thanks for your time!!
YOU MAY BE INTERESTED IN
How to Convert JSON Data Structure to ABAP Structure without ABAP Code or SE11?
Best Practices for SAP ABAP Development: A Comprehensive Guide