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. The form field must be defined with the same data type as SQL columns: Use a SCHEMA clause, TABLES section and use tabname.colname in the ATTRIBUTES section.
  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 customer number field, to retrieve only those rows where the cust_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 an SQL expression by generating an SQL condition for each field with a criteria.
    This SQL expression is stored in the character string that you specified in the CONSTRUCT statement.
  8. You can then use the SQL expression to create a STRING variable containing a complete SELECT statement.
    You must supply the WHERE keyword followed by the SQL expression produced by the CONSTRUCT.
  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.