Dynamic Where Condition usage in Database queries

Let’s get started with Dynamic Where Condition usage in Database queries. In not very many improvements we want Dynamic Where Condition for data set (DB) questions. Importance; during runtime your program might conclude which all fields of the information base table to be utilized for the DB question. We can compose our custom code for fostering those Dynamic Where Condition. Be that as it may, we know the work and torment it takes to compose such a code. Fortunately, we can utilize a Standard SAP capability module CRS_CREATE_WHERE_CONDITION to accomplish the necessity. Dynamic Where Condition usage in Database queries

Test Model:

We should consider, we have a program which will understand some rationale lastly it reaches the resolution that it should do an inquiry on Data set table MARA in view of the fields MATNR (Material Number) and MTART (Material Sort). There could be a few other potential fields in light of which the question can be made and this will be chosen at run time. The table fields and their comparing values can’t be known until run time. So we really want a Dynamic Where Condition for the Data set Inquiry. In such a condition you might decide to investigate all potential blends of fields to be utilized 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.

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?

At the point when SAP has given this FM, for what reason would it be advisable for us we set aside the time to compose code to set up our custom Dynamic Where Condition. Next time you want a Dynamic Where Proviso, you know which FM to call.

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

A Comprehensive Guide to SAP ABAP Training Online