CONSTRUCT

CONSTRUCT statement is created to run a query by example. The user can run a query on a database by specifying a value or a range of values for the screen fields that are associated with the columns in that database. The CHAR, STRING or VARCHAR variable that results from the CONSTRUCT statement contains the condition for the WHERE clause of the SELECT statement.

 

CONSTRUCT variable clause	The clause that contains a character variable used for storing the user entered search criteria, the corresponding form fields and column names
ATTRIBUTE clause	An optional ATTRIBUTE clause where the attributes for the fields used in the CONSTRUCT statement can be specified
HELP clause	An optional HELP clause where the help number for the CONSTRUCT statement can be specified
INPUT control block	An optional CONSTRUCT INPUT control block which controls the input of the data for the query

 

The CONSTRUCT statement permits the user to make a query by example by specifying ranges of values for fields of a screen form that correspond to table columns in a database. These values are converted to a single Boolean expression that may appear in WHERE clause of a prepared SELECT statement to specify search criteria. The CONSTRUCT statement can also control the environment in which search criteria are set with the help of the INPUT control block.

 

These preparations should be made in order to use the CONSTRUCT statement:

• Define fields in the form file and associate them with the database columns
• Define a variable of CHAR, STRING or VARCHAR data type
• Open and display the screen form with the help of either OPEN FORM and DISPLAY FORM statements or the OPEN WINDOW statement with the WITH FORM clause.

 

After that you can use a CONSTRUCT statement to store a Boolean expression based on the user-entered search criteria.

 

When the CONSTRUCT statement is used, the current form is activated. It is the most recently displayed form. If you use more than one 4GL window, it is the form displayed in the currently active window. The current window can be set with the help of the CURRENT WINDOW IS statement.

 

As 4GL encounters a CONSTRUCT statement at runtime, it performs these actions:

• Clears all the fields of the form contained in the FROM clause of the CONSTRUCT statement, or implied by the BY NAME clause
• Executes statements in BEFORE CONSTRUCT block of the CONSTRUCT statement, if such block is present
• Moves screen cursor to the first field of the Field list

 

The user is supposed to enter some values into the fields of the screen form. If no value is entered, any value found in the corresponding column of the specified database will match search criteria.

 

After the user has entered the values and pressed the Accept key, the CONSTRUCT statement uses AND operators to combine the entered values into a single Boolean expression that is stored in the variable of CHAR, STRING or VARCHAR type. 4GL will assign the TRUE expression "1=1" to the character variable, if no search criteria are entered.

 

The variable resulting from the CONSTRUCT statement can be used in a WHERE clause, to search the database for matching rows, if the resulting variable is combined with an SQL statement which performs a query. Usually it is combined with the SELECT statement.

 

The SELECT statement must be prepared together with the resulting variable by means of the PREPARE statement. To execute the prepared statements you can:

• Use the EXECUTE statement for any SQL statement and SELECT statement with the INTO clause
• Use an SQL cursor (with the DECLARE and FOREACH statements or with the OPEN and FETCH statements) for a SELECT statement without the INTO clause

 

When 4GL encounters the END CONSTRUCT keywords, the form is cleared.  The content of the Boolean expression is not affected by environment variables used to format data values, such as: DBDATE, DBTIME, DBFORMAT, and DBMONEY.