Dynamic Where Condition usage in Database queries

Share on Facebook13Share on LinkedIn0Tweet about this on TwitterShare on Google+0
Please Share!!

In very few developments we need Dynamic Where Condition for database (DB) queries. Meaning; during runtime your program may decide which all fields of the database 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. Thankfully, we can use a Standard SAP function module CRS_CREATE_WHERE_CONDITION to achieve the requirement.

Sample Example:

Let’s 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 Database 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 cannot be known until run time. So we need a Dynamic Where Condition for the Database 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 not be available until runtime). Please note, this program is written to explain the Dynamic Where Condition. In actual project, you would not have the parameters in selection screen. You might just know the table and field name and values in those fields would be populated at runtime.

For simplicity, we have made these fields as part of selection screen to populate our Dynamic Where Condition.

Also Read: SAP ABAP Tips

Please note above: The Dynamic Where Condition table has to be put in brackets during the SELECT statement

WHERE (l_i_output).

[adToAppearHere]

Let us check the values in debug mode.

In selection screen we provided two values to material number field and one value to material type.

Dynamic ABAP

Check the range internal table l_i_range has data from two fields.

Dynamic Where Condition

Function module CRS_CREATE_WHERE_CONDITION smartly converts the data to Dynamic Where Condition in internal table l_i_output.  Isn’t this cool? 🙂

Dynamic Where Condition

When SAP has provided this FM, why should we take the trouble to write code to prepare our custom Dynamic Where Condition. Next time you need a Dynamic Where Clause, you know which FM to call. 🙂

If you want to get such practical tweaks and tricks straight to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.

If you liked this post, please hit the share buttons. Please like us at facebook and encourage us. If you have any suggestions, criticism, comments or questions, please comment or reach out to us.

Thank you very much for your time!!

 

 

 

Image source: astutegraphics.com

Share on Facebook13Share on LinkedIn0Tweet about this on TwitterShare on Google+0
Please Share!!

2 Comments on "Dynamic Where Condition usage in Database queries"

  1. Hi,
    How are you saying that, it is a dynamic where condition. It is just using the selection screen values right??

    • Hi Venky – Thanks for the feedback.

      For this example it is straight forward with SELECTION SCREEN parameters. But the thing to take home with this article is “CRS_CREATE_WHERE_CONDITION”.

      Say, you have to select something from database based on some value of the internal table. Use this FM to create the WHERE Clause and use in the select.

      In that case you do not have values from selection screen. But still you can create the WHERE Clause. That is Dynamic.

      Hope it clarifies.

      Regards,
      Team SAPYard.

Comments are closed.