Applying dynamic Open SQL_SAP ABAP Advanced Cookbook-QQ阅读男生武侠网

上QQ阅读APP看书，第一时间看更新

Applying dynamic Open SQL

The Open SQL statement components may be specified statically or dynamically. This applies to database read statement SELECT, as well as data manipulation statements such as INSERT and UPDATE. The primary emphasis of this recipe will be on dynamic specifications of the various components of the SELECT statement. The previous recipe saw some usage of the dynamic SQL in the SELECT statement, where the table name, the target area internal table (pointed to by a field symbol), and the number rows to read using UP TO addition were specified dynamically. In addition to these, the GROUP BY, the ORDER BY, and WHERE conditions may be specified dynamically.

In this recipe, we will create a program that will take input from the user and create dynamic specifications for the where condition and the order by clause. (For simplicity's sake we emphasize on the dynamic where and order by clauses and keep the table name as spfli).

Getting ready

We will create a program that will contain a selection screen, which will allow you to take input from the user. We will create two blocks on the selection screen, namely Where and Order by.

The table to be considered for this example is spfli. The Where Clause block has one checkbox (WHERE1) used for specifying the component of the WHERE condition. Also for the Order By Clause, we have two radio buttons—orderby1 and orderby2.

How to do it...

For creating a program based on dynamic SQL, follow these steps:

Declare two string variables where_cond and order_by for specification of the dynamic where condition and order by criteria, respectively.
Next, the if statements are added for checking the options selected by the user. In this part, we will make sure that the correct criteria/condition is filled in the defined string variables.
For the ORDER BY criteria, if the user selects the first option (orderby1—no sorting), no code is added/executed. If the user has entered the sort criteria on the screen (field ordby_f) using the second radio button (orderby2), the entered name is assigned to the order_by string.
Similarly, for the Where Clause formulation, the checkbox selection is checked. If the checkbox is selected, the entered field name (field1) is concatenated with EQ and the value entered (value1).
Finally, the most important portion is added. The select statement is written using the string variables, where_cond and order_by. We make sure that select is not executed if where_cond is empty (using a check statement).
The data is read and stored in the t_spfli internal table, and outputted to the user using the loop and write statements.
It is also necessary to catch the cx_sy_dynamic_osql_error exception in case the select statement's where conditions and order by sort criteria are incorrect. Any exception occurring is caught, and the relevant message text is written using the exception class cx_sy_dynamic_osql_error.

How it works...

In the dynamic Open SQL program, there is one select statement executed that prints and fetches the data that is to be read from the database. The program checks the input entered by the user. If the where condition variable is empty, the program does not display any records from the table.

Suppose the user enters values for field1 and the value1.

Then, after the execution of the if statements related to the where condition, the where_cond variable will contain the corresponding criteria to be passed to the select statement.

The value1 variable is passed on to the where condition and the system, at runtime, evaluates their values in order to get the correct data from the database.

Similarly for the order by criteria, when the user specifies none as the sorting by, an empty string is passed to the select statement. This has no effect on the sorting, and the data is displayed as residing in the database table.

There's more...

We may use field symbols and references as shown in the previous recipe in conjunction with the code of this recipe in order to make the program work for any database table.