Customizing › Legacy Reports Customization › Report Template Reference › Report Template BLOCK Statements › Block Statement Parameters

Block Statement Parameters

blockname

Identifies the block. Each blockname must be unique.

SELECT clause

Follows the blockname and is delimited by double quotes. Lists the columns to be fetched, followed by the keyword FROM, followed by the tables from which the columns are to be fetched. It is required. Here is an example with three tables specified:

“SELECT open_date, chg_ref_num \

last_name, first_name \

FROM Change_Request, \

ca_contact”

You cannot include an SQL alias, such as:

"SELECT open_date As OpenDate"

WHERE clause

(Optional) Follows the SELECT clause and further qualifies the information selected. It may be a string constant or an expression evaluating to a string. If the WHERE clause is an empty string, all records are returned. WHERE clauses can contain replacement arguments (which refer to variables or command line arguments) using the syntax of a question mark (?). The following WHERE clause could follow the previous SELECT clause:

“WHERE #Change_Request.open_date >= ? \

AND #Change_Request.active_flag = 1 \

AND #ca_contact.last_name = ? “, $1

Note: The WHERE clause must be separate from the SELECT clause because the WHERE clause can be an expression evaluating to a string, whereas the SELECT clause is exclusively a string constant. This gives you more flexibility and data manipulation capabilities in producing your report.

SORT clause

(Optional) Follows the SELECT and WHERE clauses and sorts the fetched rows of data. The SORT clause is formatted like the SQL ORDER BY clause. Here is an example:

SORT "open_date"

Output program statements

Controls execution of the report. Before the data query, the HEADER statement, if included, prints the heater test for the block. The data query then runs. If data is returned, each statement executes in the order written, with one exception. Block functions, like sum and average, behave as thought they were at the end of the output program. In fact, their values are not stable until execution begins on the next data record.

Important! The output program depends on the success of the data query. If no data is returned from the query, then, except for the HEADER statement, the output program will not execute.