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:

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.

See also