We can help. Together we learn....

Dynamic Where Condition for usage in Data Base queries

by Chinu | Tuesday, November 18, 2008 in |

In very few developments we need Dynamic Where Condition for data base (DB) queries. Meaning; during runtime your program may decide which all fields of the data base table to be used for the DB query. We can very well write our custom code for developing those Dynamic Where Condition. But we know the effort and pain it takes to write such a code or else we can use a Standard SAP function module CRS_CREATE_WHERE_CONDITION to achieve the requirement.


Sample Example:

Lets consider, we have a program which will follow some logic and finally it comes to the conclusion that it will have to do a query on Data Base table MARA based on the fields MATNR (Material Number) and MTART (Material Type). There could be some other possible fields based on which the query can be made and this will be decided at run time. The table fields and their corresponding values can not be know until run time . So we need a Dynamic Where Condition for the Data Base Query. In such a condition you may choose to explore all possible combinations of fields to be used in the where condition and write your custom code to manufacture a Dynamic Where Condition or you can just use a Function Module (FM) which will do the job you.

We will discuss the above situation considering a program, with a selection screen having both the fields MATNR and MTART. (Let us assume these two fields and their corresponding values will be not be available until runtime).


REPORT z_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.

******************************************************************
** You may need an Dynamic Internal table in such a scenario.
So find here the link for the same **

http://help-sap.blogspot.com/search/label/Internal%20table
******************************************************************

9 comments:

  1. Voice on November 18, 2008 at 2:13 PM

    I have a query.

    If the where condition is dynamic probably i will need to build a dynamic internal table also. Is there some way to do it.

    Or whether it is advisable to select all the fields for dynamic where condition.

    :)

     
  2. Chinu on November 18, 2008 at 2:25 PM

    I have already posted a blog in this blog space regarding the dynamic internal table concept.

    Please check that one and let me know if you still have any problem.

    Thanks

     
  3. Ritesh Saurabh on December 23, 2008 at 8:51 AM

    all dynamic stuffs are done by field-symbols.

    understand one concept..do 100 stuffs.

     
  4. Ritesh Saurabh on December 23, 2008 at 9:02 AM

    most of the contents posted on this blog can be found by F1 help in sap itself. there should have higher emphasis on basic understanding of abap programming. like...limitation of 'for all entries' in data retrievel as well as its alternative way of solution

    (it is strictly a personal view.)

     
  5. Chinu on December 23, 2008 at 10:28 AM

    Ritesh,

    I really appreciate your views on the content of this Blog. But i think we will also appreciate the fact that we are not dealing with Rocket Science. We are doing something that are already and always be there in SAP. But unfortunately those contents can not be found by mere F4 help. you may some time need some body's help
    to do what a F4 help gives you. We are exactly trying to do that only.
    We are learning from each other's experiences through these Blogs.

    So by sharing tips (how ever small it may be) we can learn and this is the sole motto of this blog space.

    So welcome my friend to this small world of possibilities. :)

     
  6. Voice on December 23, 2008 at 6:13 PM

    Whatever Chinu said that is totally apt.

    And again... we write posts based on our own experience. Where we faced problems/doubt which was not resolved by F1 help but where help from some seniors or colleagues was required.

     
  7. Ritesh Saurabh on December 24, 2008 at 8:49 AM

    i agree with chinu. :) :)

     
  8. Susmitha Sanjay on April 28, 2016 at 11:30 AM

    Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog on SAP SD

     
  9. Bhavya Kumar on September 24, 2016 at 3:11 PM

    Great place to learn about the new things and got great knowledgeSAP MM Training in Chennai

     

Categories