Steps for implementing Query-by-Example

This topic describes the steps involved to implement query-by-example using the CONSTRUCT statement.

1. Define fields linked to database columns in a form specification file.
2. Define a STRING variable in your program to hold the query criteria.
3. Open a window and display the form.
4. Activate the form with the interactive dialog statement CONSTRUCT, for entry of the query criteria. Control is turned over to the user to enter his criteria.
5. The user enters his criteria in the fields specified in the CONSTRUCT statement. The CONSTRUCT statement accepts logical operators in any of the fields to indicate ranges, comparisons, sets, and partial matches. Using the form in this program, for example, the user can enter a specific value, such as "IL" in the state field, to retrieve all the rows from the customer table where the state column = IL. Or he can enter relational tests, such as "> 103", in the Store # field, to retrieve only those rows where the store_num column is greater than 103.
6. After entering his criteria, the user selects OK, to instruct your program to continue with the query, or Cancel to terminate the dialog. In this program, the action views for accept (OK) and cancel are displayed as buttons on the screen.
7. If the user accepts the dialog, the CONSTRUCT statement creates a Boolean expression by generating a logical expression for each field with a value and then applying unions (and relations) to the field statements. This expression is stored in the character string that you specified in the CONSTRUCT statement.
8. You can then use the Boolean expression to create a STRING variable containing a complete SELECT statement. You must supply the WHERE keyword to convert the Boolean expression into a WHERE clause. Make sure that you supply the spaces required to separate the constructed Boolean expression from the other parts of the SELECT statement.
9. Execute the statement to retrieve the row(s) from the database table, after preparing it or declaring a cursor for SELECT statements that might retrieve more than one row.