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).
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.
For creating a program based on dynamic SQL, follow these steps:
- Declare two string variables
where_condandorder_byfor specification of the dynamicwherecondition andorder bycriteria, respectively.
- Next, the
ifstatements 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 BYcriteria, 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 (fieldordby_f) using the second radio button (orderby2), the entered name is assigned to theorder_bystring. - Similarly, for the Where Clause formulation, the checkbox selection is checked. If the checkbox is selected, the entered field name (
field1) is concatenated withEQand the value entered (value1).
- Finally, the most important portion is added. The
selectstatement is written using the string variables,where_condandorder_by. We make sure thatselectis not executed ifwhere_condis empty (using acheckstatement).
- The data is read and stored in the
t_spfliinternal table, and outputted to the user using theloopandwritestatements. - It is also necessary to
catchthecx_sy_dynamic_osql_errorexception in case theselectstatement'swhereconditions andorder bysort criteria are incorrect. Any exception occurring is caught, and the relevant message text is written using the exception classcx_sy_dynamic_osql_error.
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.