The CONSTRUCT sub-dialog

The CONSTRUCT sub-dialog provides database query by example feature, converting search criteria entered by the user into an SQL WHERE condition that can be use to execute a SELECT statement.

Defining query by example fields

The CONSTRUCT sub-dialog requires a character string variable to hold the WHERE clause, and a list of screen fields where the user can enter search criteria.

DEFINE sql_condition STRING
  ...
DIALOG
  CONSTRUCT BY NAME sql_condition
      ON customer.cust_name, customer.cust_address 
    BEFORE FIELD cust_name
    ...
  END CONSTRUCT
  ...
END DIALOG

Make sure the character string variable is large enough to store all possible SQL conditions. It is better to use a STRING data type to avoid any size problems.

CONSTRUCT uses the field data types defined in the current form file to produce the SQL conditions. This is different from other interactive instructions, where the data types of the program variables define the way to handle input/display. It is strongly recommended (but not mandatory) that the form field data types correspond to the data types of the program variables used for input. This is implicit if both form fields and program variables are based on the database schema file.

The CONSTRUCT clause can be used in two forms:

CONSTRUCT BY NAME string-variable ON column-list
CONSTRUCT string-variable ON column-list FROM field-list

The BY NAME clause implicitly binds the form fields to the columns, where the form field identifiers match the column names specified in the column-list after the ON keyword. You can specify the individual column names (separated by commas) or use the tablename.* shortcut to include all columns defined for a table in the database schema file.

The FROM clause explicitly binds the form fields listed after the FROM keyword with the column definitions listed after the ON keyword.

In both cases, the name of the columns in column-list will be used to produce the SQL condition in string-variable.

Identifying a CONSTRUCT sub-dialog

The name of a CONSTRUCT sub-dialog can be used to qualify sub-dialog actions with a prefix. In order to identify the CONSTRUCT sub-dialog with a specific name, use the ATTRIBUTES clause to set the NAME attribute:

CONSTRUCT BY NAME sql_condition ON customer.*
  ATTRIBUTES (NAME = "q_cust")
  ...

Control blocks in CONSTRUCT

A Query By Example declared with the CONSTRUCT clause can raise the following triggers:

In the singular CONSTRUCT instruction, BEFORE CONSTRUCT and AFTER CONSTRUCT blocks are typically used as initialization and finalization blocks. In DIALOG instruction, BEFORE CONSTRUCT and AFTER CONSTRUCT blocks will be executed each time the focus goes to (BEFORE) or leaves (AFTER) the group of fields defined by this sub-dialog.