# CHAPTER 7: Command Reference

## 7.1 The Empress Report Language

This chapter is the complete reference for the Empress Report Writer report generating language. The following topics are covered:

- Assignments

- Attributes

- Blank pages

- Calculations

- Character strings

- Column control

- Column placing

- Column width

- Comments

- Conditions Dates

- Definitions

- Empty statements

- Group statements

- Headers and footers

- Including files

- Joins

- Line and page control

- Justifying text

- Loops

- Notation

- Null values

- PRINT commands

- Page length

- Page margins

- Page numbering

- Parameters

- Pattern matching

- Picture formats

- PRINT statements

- Record statements

- Script structure

- SELECT statements

- Sorted output

- Specifying a database

- Variables

- Variables in select statements

- WHERE clauses

### 7.1.1 Report Writer Commands

The following sections comprise the reference to the Empress Report Writer command language. Each entry contains:

- A brief synopsis

- A syntax diagram

- Relevant notes

- Examples of use

The formal syntax for an Empress Report Writer script is:

    [PARAMETER]
    statement {statement}

The order in which the sections of an Empress Report Writer script are arranged is quite flexible. The major constraint is that any `PARAMETER` specifications must be placed at the very beginning of the script.

If a default database name is set, it must be done before any `SELECT` statements that assume the default database. If it is not passed in as a `PARAMETER`, it must be set in a `LET` statement.

There can be many `SELECT` statements in a single script, but they must appear before any attempt to use the attribute values they retrieve. `SELECT` statements are optional; Empress Report Writer can be used as a standalone report writer drawing data from files with the `READ` statement, from a file specified with the `-f` option when Empress Report Writer is invoked, or using data passed from Empress 4GL.

Any `DEFINE` statements must come immediately after their `SELECT` statements and before any other statements.

Page width and length other than the defaults should be set before any headers and footers are specified, so that these will use the correct values.

Headers and footers may be specified before the `SELECT` statements if they do not include attributes or derived attributes; otherwise, they must come after them. (HEADERs and FOOTERs can be changed later in a script.)

Semicolons (;) are empty statements and they may appear anywhere that a statement may.

## 7.2 DEFINE Statement

The `DEFINE` statement sets up derived attributes which are constructed from the values of other attributes. `DEFINE` statements can include all arithmetical calculations except `MAX`, `MIN`, `SUM`, `COUNT`, and `AVERAGE` since these are aggregate functions that apply to groups of attribute values, not single values. (Arithmetic calculations are discussed under the `LET` statement.)

A `DEFINE` statement must follow a `SELECT` statement and can define derived attributes based only on the attributes selected. You can consider a `DEFINE` statement as creating a new attribute which can then be used and manipulated as though it were an ordinary attribute. Hence, these derived attributes can also be used in `FOR` statements.

Syntax

`DEFINE variable = expression [;]`

where:

`expression` | must include at least one attribute.

Example

The following definition creates a derived attribute named `income` from the attributes `rate` and `hours`:

`DEFINE income = rate * 1.5 * attr "hours";`

These derived attributes can have the functions `MAX`, `MIN`, `COUNT`, `SUM`, `AVERAGE`, and `MAXWIDTH` performed on them, just as though they were ordinary attributes. In fact, using a derived attribute to sum, average, count, etc. calculations on attributes is essential, since these functions take only attributes or derived attributes as arguments, not expressions.

## 7.3 Empty Statement

The empty statement, which consists of a single semicolon, is perfectly legal when using Empress Report Writer. Unnecessary semicolons will not result in error messages.

## 7.4 Expressions

An expression is one of:

    simple_expression
    simple_expression format_options

A simple_expression is one of:

    variable
    attribute
    character string
    number

`simple_expression` |
    |  +
    |  -
    |  *
    |  /
    |  %
    |  =
    |  !=
    |  <
    |  <=
    |  >
    |  >=
    | simple_expression
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |

`simple_expression CONCAT simple_expression`

    |NOT
    | !
    |simple_expression
    |

`simple_expression` |
    | AND
    | OR
    | &
    | |
    | simple_expression
    |
    |
    |

    - simple_expression
    ( expression )

    AVERAGE [OF] attribute [WHERE simple_expression]
    COUNT [OF] attribute [WHERE simple_expression]
    | SUM | [OF] attribute [WHERE simple_expression]
    | MAX |
    | MIN |

    MAXWIDTH [of] ATTRIBUTE [where SIMPLE_EXPRESSION]

    | day
    | month
    | year
    | [OF] (
    |
    |
    | date_attribute
    | date_variable
    | )
    |

    MAKEDATE (year_value, month_value, day_value)

format_options are described under the section on "PRINT Statements''.

### 7.4.1 Calculations

Calculations can be used in `DEFINE` statements, conditions, `LET` statements, or `PRINT` statements. The following operators are available for calculations:

Table 7-1: Calculation Operators

Operator | Meaning
`+` | Addition
`-` | Subtraction
`*` | Multiplication
`/` | Division
`%` | Remainder
`AVERAGE [OF]` | Average
`COUNT [OF]` | Count
`MAX [OF]` | Maximum
`MAXWIDTH [OF]` | Maximum width of an attribute value
`MIN [OF]` | Minimum
`SUM [OF]` | Sum

Words enclosed in square brackets (`[]`) in the above table are optional. Thus `sum total` and `sum of total` are equivalent constructions. Additional operators for string and date expressions are listed under the section on "Expressions''.

Some typical calculations are shown below:

    LET total = SUM amount;
    PRINT mark / totalmarks * 100, percent;
    PRINT "This leaves", value % 4,
         "remaining after distribution.";
    DEFINE newrate = rate * 1.25;
    LET netpay = gross - deductions;
    LET colwidth = (MAXWIDTH OF amount
         WHERE name = `Jones') + 2;

Parentheses may be used to group operations together:

`LET percentage = ((amount+interest) / (number-1)) * 100;`

Calculations may be purely numerical. For example, the PRINT statement:

`PRINT 5*4/2;`

multiplies 5 by 4, divides the answer by 2, and prints the result, which is 10.

One point that should be stressed is that integer arithmetic is used where all the values in a calculation are integers. Thus the statement:

`LET a = 3/4;`

results in a being assigned a value of 0, not 0.75. Take care to ensure that at least one value has a decimal point when this effect is not wanted.

### 7.4.2 Date Functions

Date attributes and variables passed in as `DATE` parameters can have their values printed or used in comparisons with other date values, but cannot be used in calculations or in comparisons with non-date values. To use a date value in calculations, the day, month, or year must be obtained in numerical form by the functions `day of`, `month of`, and `year of`, respectively. The resulting integers can be used in calculations and the final results re-converted to a date by the function `MAKEDATE`.

The syntax for these functions is:

    | day
    | month
    | year
    | [OF] (
    |
    |
    | MAKEDATE function
    | date_variable
    | )
    |

which produces an integer from 1 to 31 for `day of`, from 1 to 12 for `month of`, and from 0 to 9999 for `year of`.

`MAKEDATE` takes three integers (or expressions which produce integers) and converts them to the day, month, and year for a new date. Its syntax is:

`MAKEDATE (year_value, month_value, day_value)`

A valid date is created by first adjusting the year and month values until the month is in the range 1 to 12, and then adjusting the month and year values until the day value fits into the proper range for that month. Hence, to perform `MAKEDATE (1990, 27, 45)`, the year is first altered to 1990 + 2 years + 3 months, bringing the date to March 31, 1992. The entire 30 days of April, the next month, can be filled in from the day value of 45, so the month is increased to May. This leaves 15 days remaining, a legal day value for May, so the entire date becomes May 15, 1992.

Specifying zero for a day value gives the last day of the previous month; specifying zero for a month value gives the last day of the previous year. Hence, specifying both month and day values as zero gives November 30 of the previous year, since the zero month moves the date back to December before the zero day moves it to the end of November. So to get the last day of a given year, you need `MAKEDATE (YEAR, 1, 0)`.

The following example finds and prints a date two months later than the date contained in the attribute `date_one` :

    LET day1 = DAY OF (date_one);
    LET month1 = MONTH OF (date_one);
    LET year1 = YEAR OF (date_one);
    PRINT MAKEDATE (year1, month1 + 2, day1)
    PICTURE "mm dd yy",
    NEWLINE;

If the value of `date_one` is June 12, 1992, the `PRINT` statement will produce `8 12 92`; if the value of `date_one` is November 22, 1992, it will produce `1 22 93`.

Note:

The `MAKEDATE` function assumes that a month has 31 days (i.e. +1 month is the same as +31 days).

## 7.5 FOR Statement

Under `FOR` statements there are:

- `GROUP` statements

- grouping by date

- `RECORD` statements

The `GROUP` and "grouping by date'' statements are for grouping records so that actions can be applied repeatedly to groups of records. The `RECORD` statement allows actions to be repeated for single records.

Up to 22 `FOR` statements can be nested.

### 7.5.1 GROUP Statement

Operations to be performed on a group of records are enclosed in `GROUP` statements. The syntax for a `GROUP` statement is:

Syntax

    FOR [EACH] GROUP [OF] attribute {, attribute}
           [IN [CONTEXT] name] [BECOMES [CONTEXT] name]
           statement {statement}
    END [;]

Any attribute used in a `GROUP` statement must have been previously sorted in a `SELECT` statement to which the `GROUP` statement refers, or the grouping will not work properly. Moreover, if grouping is done over several attributes, the order in which attributes are used to group the data must correspond to the order in which the attributes were used to sort the data.

The phrase `IN CONTEXT name` allows the `GROUP` statement to refer to the records retrieved by a specific `SELECT` statement, which have been labeled as that context. Thus a second `GROUP` statement nested inside a first may refer to records from an entirely different `SELECT` statement. (Do not confuse an Empress Report Writer context with an Empress 4GL context passed to Empress Report Writer. Empress 4GL contexts passed to Empress Report Writer do not have a context name; their records are available to Empress Report Writer exactly as if they were read from a file with the `-f` option of `emprepwr`).

Similarly, labeling the `GROUP` statement itself as a context allows its records to be referred to by name later within the `FOR ... END` block. For example, a `GROUP` statement might have two `RECORD` statements nested within it, one referring to the records of the group itself, and the other referring to the records from another `GROUP` statement or a `SELECT` statement.

A typical `GROUP` statement might be as follows:

    FOR EACH GROUP OF name
            PRINT name, ":", COLUMN 15, "$",
            SUM OF amount, NEWLINE;
    END;

If a table has the following data:

    name          amount     day

    Smith          30        Jun 12, 92
    Jones          40        Jun 4, 92
    Smith          25        Jun 15, 92
    Smith          15        Jun 15, 92
    Jones          15        Jun 4, 92
    Brown          25        Jun 20, 92
    Jones          30        Jun 22, 92

then from a `SELECT` statement sorting by name, the above `GROUP` statement would produce:

    Brown:     $25
    Jones:     $85
    Smith:     $70

To illustrate nested `GROUP` statements from the table above, the `SELECT` needs to sort by name and day. The following statements:nested group statements

    FOR EACH GROUP OF name
            PRINT name, ":";
            FOR EACH GROUP OF ATTR 'day'
                    PRINT COLUMN 15, ATTR 'day',
                    COLUMN 30, "$",
                    SUM OF amount, NEWLINE;
            END;
            PRINT COLUMN 29, "-----", NEWLINE;
            PRINT COLUMN 20, "Total":, COLUMN 30, "$",
            SUM OF amount, NEWLINE;
            NEWLINE;
    END;

produce:

    Brown:    92/6/20          $25
                             -----
              Total:           $25

    Jones:    92/6/4           $55
              92/6/22          $30
                              -----
              Total:           $85

    Smith:    92/6/12          $30
              92/6/15          $40
                              -----
              Total:           $70

Note

The calculation "`SUM OF amount`'' has a different effect depending on whether it is placed in the inner or outer `GROUP` statement.

### 7.5.2 Grouping by Date

`GROUP` statements can also be used to group by week, month, or year on an Empress `DATE` data type attribute. Again, to do this the data must have been sorted on the `DATE` attribute when it was selected.

The statements to group by date are:

    FOR [EACH] YEAR [OF] attribute ENDING month [,] daynumber
           [IN [CONTEXT] name] [BECOMES [CONTEXT] name]
           [statement {statement}]
    END [;]

for year groupings, where `month` is the month name, in any mixture of upper and lower case (month names are keywords), and `daynumber` is the day number.

Note

The month must be spelled in full. For example:

`FOR EACH YEAR OF attribute ENDING December 31`

For monthly groupings:

    FOR [EACH] MONTH [OF] attribute ENDING daynumber
           [IN [CONTEXT] name] [BECOMES [CONTEXT] name]
           statement {statement}
    END [;]

with `daynumber` replaced by the number of the day. For example:

`FOR EACH MONTH OF attribute ENDING 30`

Note

For each month of attribute ending 31 will handle February, April, June, September, and November correctly, giving their last days as expected.

For week groupings:

    FOR [EACH] WEEK [OF] attribute ENDING dayname
           [IN [CONTEXT] name] [BECOMES [CONTEXT] name]
           statement {statement}
    END [;]

where:

`dayname` | is one of: `SUNDAY`, `MONDAY`, `TUESDAY`, `WEDNESDAY`, `THURSDAY`, `FRIDAY`, `SATURDAY` in any mixture of upper and lower case (day names are keywords). The `dayname` must be spelled in full, for example: `FOR EACH WEEK OF attribute ENDING SATURDAY`
`daynumber` | is an integer from 1 to 31, inclusive.
`month` | is one of: `JANUARY`, `FEBRUARY`, `MARCH`, `APRIL`, `MAY`, `JUNE`, `JULY`, `AUGUST`, `SEPTEMBER`, `OCTOBER`, `NOVEMBER`, `DECEMBER`

### 7.5.3 RECORD Statement

`RECORD` statements are similar to `RECORD GROUP` statements, but operate on all records of a group or all records retrieved by a `SELECT` statement, as appropriate, rather than on groups of records. The syntax for `RECORD` statements is:

    FOR [EACH] RECORD [IN [CONTEXT] name]
           [BECOMES [CONTEXT] name]
           [statement {statement}]
    END[;]

The statements contained within a `RECORD` statement are executed for every record currently being considered. For every record of a group if the `RECORD` statement is nested within a `GROUP` statement, every record in the named context, or every record that has been selected if the `RECORD` statement is not nested or operating on a named contextscontext.

The `BECOMES CONTEXT` clause can be used to build up a named context of records as they are processed. At any given time, the context will contain the records that have been processed in the `FOR ... END` block.

As an example of a non-nested `RECORD` statement, if a table `dates` contains the following data:

    name          day

    Jones         Jun 12, 92
    Smith         Jun 13, 92
    Jones         Jun 22, 92
    Jones         Jun 24, 92
    Smith         Jun 27, 92

then the following report script:

    SELECT FROM dates SORT BY name;
    FOR EACH RECORD
           PRINT name, COLUMN 15, ATTR 'day' PICTURE
           "dd aaaaaaaaa yyyy";
           NEWLINE;
    END;

would produce:

    Jones          12 June 1992
    Jones          22 June 1992
    Jones          24 June 1992
    Smith          13 June 1992
    Smith          27 June 1992

The following illustrates a RECORD statement nested inside a GROUP statement:

    SELECT FROM dates SORT BY name;
    FOR EACH GROUP OF name
           PRINT name;
           FOR EACH RECORD
                  PRINT COLUMN 15, ATTR 'day' PICTURE
                  "dd aaaaaaaaa yyyy";
                  NEWLINE;
           END;
           NEWLINE;
    END;

which yields the following:

    Jones     12 June 1992
              22 June 1992
              24 June 1992

    Smith     13 June 1992
              27 June 1992

## 7.6 HEADER/FOOTER

Headers and footers are groups of lines which are printed at the beginning and end of every page. They are also known as "running titles''.

The syntax for headers and footers is:

Syntax

    | HEADER| [name]
    | FOOTER|
           statement {statement}
    END [;]

If a `name` (which may be any normal variable name) is used, future statements of the form:

    USE
    | HEADER
    | FOOTER
    | name [;]
    |

will cause the named header to appear on the next and subsequent pages, and the named footer to be used at the bottom of the current and subsequent pages. However, if there is not enough space on the current page to print the new footer, the old one will be used. This statement enables a single report to use different headers and footers on different pages.

Note that `TOLINE` and `NEWPAGE` statements are not permitted in headers and footers.

Headers and footers are usually four, five, or six lines long, but they may be as short or long as desired. The sum of the lines in the header and footer must not exceed the number of lines on the page.

A typical header might be:

    HEADER
          NEWLINE 3;
          PRINT "Annual Report", NEWLINE;
          NEWLINE 2;
    END;

This is a 6-line header with the character string "`Annual Report`'' left-justified on the fourth line.

A typical footer might be:

    FOOTER
          NEWLINE 2;
          PRINT PAGENUMBER, NEWLINE;
          NEWLINE 2;
    END;

This is a 5-line footer with the page number left-justified on the third line.

The following header prints a page number on the outside edge of a page - on the left for an even page, and on the right for an odd one:

    HEADER
          IF (PAGENUMBER % 2 = 0)  /* even page */
                PRINT PAGENUMBER, NEWLINE;
          ELSE
                         /* odd page */
                PRINT PAGENUMBER WIDTH PAGEWIDTH RIGHT;
          END;
    END;

while loops are not allowed in headers and footers.

## 7.7 IF Statement

Different sets of statements may be executed depending on the result of a test to see whether an expression is true or false. These tests are made by conditions. Conditions have the form:

    IF expression
           [THEN]
           [statement {statement}]
    [ELSE
           [statement {statement}]]
    END [;]

The expression is a logical expression and must test whether or not one value is equal to (=), not equal to (!=), greater than (>), less than (<), greater than or equal to (>=), or less than or equal to (<=), another value. Either value may be a numerical constant, a variable, or an attribute. Parentheses may be used to group logical expressions.

Note, however, that the expression is not boolean; that is, `IF (1)` and `IF (0)` are not allowed.

If two expressions are separated by `AND` or "&'', both must be true for the statements following the `IF` to be executed. If `OR` or " | '' is used to separate expressions, the statements are executed if either or both are true.

Conditions provide a means of testing for particular values in retrieved or calculated results or controlling layout of output. For example, the following statements test whether the line number is greater than 45 and start a new page if it is:

    IF LINENUMBER > 45
            NEWPAGE;
    END;

The next example tests whether the value of the variable amount is less than zero, and prints an appropriate message depending on the result:

    IF amount < 0
          PRINT "Account Overdrawn", NEWLINE;
    ELSE
          PRINT "Current balance: ", amount PICTURE
          $**9.99, NEWLINE;
    END;

Note that the `ELSE` section of a condition may be omitted if desired. If nothing is to be done when the `IF` expression is true, the `ELSE` may follow straight after it, or an empty statement may be placed immediately after the `IF` to emphasize that nothing is done:

    IF expression
    ELSE
          statements
    END;

or:

    IF expression
          ;
    ELSE
          statements
    END;

## 7.8 INCLUDE Statement

The keyword `INCLUDE` followed by an operating system file name instructsEmpress Report Writer to read commands from the named file. The file may contain any Empress Report Writer commands.

Syntax

    INCLUDE file [;]

where:

`file` | must be enclosed in quotes.

For example, we have a file called `logo` which contains the following:

    PRINT "FOR THE BEST IN PARTS AND SERVICE --
          UNIVERSAL AUTOMOTIVE SERVICES"
          WIDTH PAGEWIDTH CENTER, NEWLINE;
    PRINT "1757 El Camino Real, Bayville, CA 92308" WIDTH
    PAGEWIDTH CENTER,
          NEWLINE;

If we want this printed at the top of a letter, all we have to do is use:

`INCLUDE 'logo';`

at the appropriate place in the script. It should be noted that the `INCLUDE` statement is expanded before the first line of a given script is executed.

## 7.9 LET Statement

Character strings, numeric constants, and the results of arithmetic calculations (sum, average, addition, subtraction, multiplication, division, etc.) may be stored in variables by means of a `LET` statement, also known as an "assignment''. These variables may then be printed or used in calculations.

A `LET` statement is:

Syntax

`LET variable = expression [;]`

where:

`variable` | is described under the "Conventions" section at the beginning of this chapter.
`expression` | is defined under the Expression" section

For example, the statements:

    LET pagelength = 45;
    LET price = 25.50;
    LET datepicture = dd-mm-yy;
    LET netpay = gross - deductions;

set the values of the variables `pagelength`, `price`, `datepicture`, and `netpay`, respectively, to the numeric constants `45` and `25.50`, the character string `dd-mm-yy`, and the result of subtracting the value of the variable `deductions` from the value of the variable `gross`.

Functions such as sums and averages may also be assigned to variables. For example:

    LET total = SUM OF income;
    LET colwidth = (MAXWIDTH OF amount
            WHERE name = 'Jones') + 2;

## 7.10 PARAMETER Statement

A number of parameters may be specified at the time the report is run. These parameters are assigned to variables in the order they are passed to Empress Report Writer, and these variables may then be used like any others.

`PARAMETER`s may be of five types:

- character strings

- dates

- decimal numbers

- float numbers

- integers

Character strings must be enclosed in quotes when they are passed to Empress Report Writer if they contain any blanks, tabs, or new lines.

Date parameters must be passed to Empress Report Writer in universal date format (`yyyymmdd`) or in the `year/month/day` order specified by `MSDATEPIC`. For instance, June 2, 1992 can be passed to Empress Report Writer in universal date format as 1`9920602`. Or, if `MSDATEPIC` has been set to "`AAAAAAAAA DD, YYYY`'', the same date can be passed as `June 2, 1992`. Note that although these date parameters may resemble integers, they are not numeric values and may not be used for arithmetic. They may only be used in `SELECT` statements or printed using date picture formats.

Decimal parameters hold integers or numbers with decimal places, for example, `11`, `4192`, `22.9`, `.00084`, etc.

Float parameters are for exponential numbers exponential numbers, e.g., `1.02000e+2`, `4.90000E-04`, etc.

Integer parameters hold integers.

Parameters are collected and assigned to variables at the beginning of an Empress Report Writer script. The syntax for specifying parameters is:

Syntax

    PARAMETERS
         type variable [;] {type variable [;]}
    END [;]

where:

`type` | is one of `CHAR`, `DATE`, `DECIMAL`, `FLOAT` or `INTEGER`. These lines perform the actual assignments.

For example:

    PARAMETERS
          DATE startdate
          DECIMAL amount
          CHAR title
    END;

These lines tell Empress Report Writer to expect one date, one decimal, and one character string parameter, to be assigned to variables called `startdate`, `amount`, and `title`, respectively. If the script for this report is contained in a file called `script`, then giving the command:

    emprepwr script 19920228 24 "UNIVERSAL AUTOMOTIVE SERVICES"

will store the date February 28, 1992 in the variable `startdate`, the number 24 in the variable `amount`, and the string "U`UNIVERSAL AUTOMOTIVE SERVICES`'' in the variable `title`.

If more or fewer parameters are passed to Empress Report Writer than the script expects to collect, an error message will be printed.

## 7.11 PRINT Statement

All output in an Empress Report Writer report is produced by `PRINT` statements. A `PRINT` statement takes one or more arguments, separated by commas. The arguments may be print commands or print items.

A print statement is:

    | print_command
    | PRINT print_item {, print_item}
    | [;]
    |

No space is left between output from the different arguments to a `PRINT` statement unless it is requested. Spacing is done by means of print commands, which may be associated with other arguments or appear as statements by themselves.

Print commands indicate what columns, lines, or pages output is to be printed on, or that a file of data is to be printed. All the print commands are listed below:

    |COL   | expression
    |COLUMN|
    FILE filename
    NEWLINE [expression]
    NEWPAGE [expression]
    TAB [expression]
    TABCLEAR
    TABSET integer {integer}
    TOLINE expression

You cannot call a variable by any of these names, and if you have an attribute called by one of these, you must identify it as an attribute by placing the keyword `ATTR` in front of it and enclosing it in quotes (treating it as a complex Empress attribute name).

A print item is one of:

    print_command
    expression [format_option {format_option}]

Format options are:

    | PICTURE |  expression
    | PIC     |
    WIDTH expression
    PRECISION expression
    LEFT
    RIGHT
    LEFTRIGHT
    | CENTER  |
    | CENTRE  |
    OVERFLOW INTO variable
    OVERPRINT
    WRAPMARGIN expression

Where more than one `format_option` is used in a `print_item`, the order of the options must follow the order of the list above.

For example, the statements:

    COLUMN 25;
    PRINT "A TITLE", NEWLINE 2, "A label:";
    PRINT COLUMN 15, "A passage of text";

produce:

                              A TITLE

    A label:      A passage of text.

In these statements, the print command `COLUMN 25` is a complete statement, while `NEWLINE 2` and `COLUMN 15` are given as arguments to another `PRINT` statement. Note that no new line is left between successive print items unless requested.

The following examples illustrate the use of the width and simple justification instructions:

    PRINT COLUMN 5, "Daffodil" WIDTH 20, "yellow", NEWLINE;
    PRINT COLUMN 5, "Rose" WIDTH 20, "pink", NEWLINE;
    PRINT COLUMN 5, "Carnation" WIDTH 20, "red", NEWLINE;
    PRINT COLUMN 5, "Chrysanthemum" WIDTH 20, "white", NEWLINE;

produces:

    Daffodil           yellow
    Rose               pink
    Carnation          red
    Chrysanthemum      white

If the same statements have a centering instruction added:

    PRINT COLUMN 5, "Daffodil" WIDTH 20 CENTER, "yellow", NEWLINE;
    PRINT COLUMN 5, "Rose" WIDTH 20 CENTER, "pink", NEWLINE;
    PRINT COLUMN 5, "Carnation" WIDTH 20 CENTER, "red", NEWLINE;
    PRINT COLUMN 5, "Chrysanthemum" WIDTH 20 CENTER, "white", NEWLINE;

they now produce:

         Daffodil        yellow
           Rose          pink
         Carnation       red
       Chrysanthemum     white

If the `CENTER` instruction is changed to `RIGHT`:

    PRINT COLUMN 5, "Daffodil" WIDTH 20 RIGHT, "yellow", NEWLINE;
    PRINT COLUMN 5, "Rose" WIDTH 20 RIGHT, "pink", NEWLINE;
    PRINT COLUMN 5, "Carnation" WIDTH 20 RIGHT, "red", NEWLINE;
    PRINT COLUMN 5, "Chrysanthemum" WIDTH 20 RIGHT, "white", NEWLINE;

the effect now becomes:

                Daffodilyellow
                    Rosepink
               Carnationred
           Chrysanthemumwhite

As an illustration of what happens if data is too wide to fit the column allotted to it, the statement:

    PRINT COLUMN 10, "Hippopotamus" WIDTH 8, COLUMN 25,
            "River Horse";

produces:

`Hippopot River Horse`

The next example illustrates using the `OVERPRINT` keyword to underline a heading:

    PRINT Annual Report 1992, OVERPRINT, "______ ______ ____", NEWLINE;

produces:

    "Annual" "Report" "1992"

The system variable `POSITION` always holds the current column number, and may be used to check what column in the line printing has reached. For example, an index with references linked to their page numbers by a row of dots could be printed by the following:

    FOR EACH RECORD
          PRINT reference,  ;
          WHILE (POSITION < 61)
                DO
                PRINT ".";
          END;
          PRINT number WIDTH 4 RIGHT, NEWLINE;
    END;

to produce output like:

    attributes..........................   47
    select statements...................   53
    system variables....................   69
    WRAPMARGIN..........................  104

### 7.11.1 Line and Page Control

The keywords `NEWLINE`, `TOLINE` and `NEWPAGE` are used for line and page control. These may be used as independent statements or as arguments to a `PRINT`. `NEWLINE` and `NEWPAGE` take an optional argument specifying the number of new lines or pages to be printed. The argument must be an unsigned, positive integer. `TOLINE` takes an argument specifying the line number on which printed output should next appear; if the argument exceeds the number of lines on the page, a `NEWPAGE` is generated; if the argument is for a line preceding the current line, a `NEWLINE` is generated.

Syntax

    | NEWLINE | [expression] [;]
    | NEWPAGE |
    TOLINE expression [;]

The statement:

`NEWLINE;`

will terminate the current line and move down to the next one, so that:

    PRINT "Elephant", NEWLINE;
    PRINT "Gryphon", NEWLINE;

produces:

    Elephant
    Gryphon

and leaves Empress Report Writer at the beginning of a fresh line, ready to print.

Since `PRINT` statements do not automatically start on a new line, we recommend ending `PRINT` statements with a `NEWLINE` instruction.

To leave a blank line in your output, two consecutive `NEWLINE` instructions are needed, or a single `NEWLINE` instruction with the argument 2, as shown below. The first example is the style we recommend.

    PRINT "Elephant", NEWLINE;
    NEWLINE;
    PRINT "Gryphon", NEWLINE;

and

    PRINT "Elephant";
    NEWLINE;
    NEWLINE;
    PRINT "Gryphon", NEWLINE;

and

    PRINT "Elephant";
    NEWLINE 2;
    PRINT "Gryphon", NEWLINE;

and

    PRINT "Elephant", NEWLINE, NEWLINE;
    PRINT "Gryphon", NEWLINE;

and

    PRINT "Elephant", NEWLINE 2;
    PRINT "Gryphon", NEWLINE;

and

    PRINT "Elephant", NEWLINE 2, "Gryphon",
    NEWLINE;

all produce:

    Elephant
    Gryphon

The statements:

    TOLINE 15;
     PRINT "Title", NEWLINE;

and

    PRINT TOLINE 15, "Title", NEWLINE;

both print the word `Title` all by itself on line 15 of the page.

The statements:

    PRINT "Warthog", NEWPAGE;

and

    PRINT "Warthog";
    NEWPAGE;

both print the word `Warthog` and then start a new page.

The following example ensures that the next output will be printed on an odd pages-odd-numbered page, such as for beginning a new section of a long report:

    NEWPAGE (pagenumber % 2) + 1;

If the page number is even page, `pagenumber % 2` is zero, so a `NEWPAGE 1` instruction is given; if it is odd, `pagenumber % 2` is one, and a `NEWPAGE 2` instruction results.

### 7.11.2 Column Control

Printing in columns is controlled by the following keywords:

    CENTER, CENTRE
    COLUMN, COL
    LEFT
    LEFTRIGHT
    OVERFLOW INTO
    OVERPRINT
    RIGHT
    TAB
    TABCLEAR
    TABSET
    WIDTH

### 7.11.3 Column Placing

The keyword `COLUMN` followed by a number will space across the current line to the indicated column, and begin printing in that column. `COLUMN` may be abbreviated to `COL` if desired. More than one column request may be given in the same `PRINT` statement, but requests should always print from left to right on the line unless overprinting is explicitly requested, or warning messages will be produced. Column requests which start in the middle of something else will produce a warning message; the output will be overprinted.

If overprinting is requested, a carriage return is printed and column numbering is reset to one. This allows complex overstruck characters to be produced (e.g., \xb1 ), or words to be underlined or emboldened by overstriking. (Backspaces within a character string may also be used to print overstruck characters.)

The system variable `POSITION` always holds the column number on the current line; an example of its use is given in the section on `PRINT` statements. Attempting to alter `POSITION` will have no effect on a report.

### 7.11.4 Columns and Page Margins

All column requests are counted from the left edge of the page, which is column 1. Printing which would extend beyond the specified right margin of the page (set by the system variable `PAGEWIDTH`) produces a warning message. If printing is wider than a column specified for it, the output is simply truncated, with no warning message.

Output may be wrapped around at the edge of a page or a column by using the `OVERFLOW INTO` keywords discussed in the section on `PRINT` statements, and the system variable `WRAPMARGIN` may be used to prevent words being broken in the middle.

### 7.11.5 Column Width and Justification

Data may be printed in a column of specified width, and left-justified, centered, right-justified, or both left- and right-justified within this column. Width is specified by the keyword `WIDTH`, followed by the width in characters. If the data to be printed is too wide to fit in the column width specified, it is truncated at the right margin of the column. If the output is to be wrapped around, the left-over data must be directed to a variable via the `OVERFLOW INTO` keywords (see the section on `PRINT` statements), and that variable printed.

The justification instructions, which are `LEFT`, `CENTER`, `RIGHT`, and `LEFTRIGHT`, are always used with a width specification. Left-justification is the default for all data if no justification instructions are given. `CENTER` may be spelled `CENTRE` if preferred.

### 7.11.6 Multi-Line Values OVERFLOW, LEFTRIGHT, WRAPMARGIN

If a data value is to be printed over several lines, the data which overflows the column width on each line should be directed into a variable. The variable is then printed and its overflow directed into the variable again, and so on, until the entire value is printed. The general form for this is:

    PRINT variable_1 WIDTH width OVERFLOW INTO variable_2,
    NEWLINE;
    WHILE (variable_2 != " ")
          DO
          PRINT variable_2 WIDTH width OVERFLOW INTO
          variable_2, NEWLINE;
    END;

`variable_1` may be used throughout, but of course this destroys its original value.

To truncate output to a given width, use the `OVERFLOW INTO` syntax without printing the overflow data.

This is best illustrated by examples. We will use the following character string, which has been assigned to a variable called `string`:

    'I am sure you will find the hippopotamus quite
    satisfactory, sir, 'the trainer said, smiling ingratiatingly.

`At this point the creature rolled over, showering us with mud. 'Ah, yes ... er, well, I suppose so,' Johnson replied, brushing vaguely at his trouser legs.`

To simply print this string in a column 45 characters wide, use:

    PRINT string WIDTH 45 OVERFLOW INTO temp, NEWLINE;
    WHILE (temp != "")
          PRINT temp WIDTH 45 OVERFLOW INTO temp, NEWLINE;
    END;

This directs the leftover characters into a variable named temp after each line is printed; temp grows smaller and smaller until all its characters have been printed. The output is:

    'I am sure you will find the hippopotamus qui
    te satisfactory, sir,'
    the trainer said, smiling ingratiatingly.

    At this point the creature rolled over, showed
    ring us with mud.
    'Ah, yes ... er, well, I suppose so,' Johnson
    replied brushing
    vaguely at his trouser legs.

Note

Words are simply broken in the middle at the column edge, and new lines and blank lines in the string are preserved. Lines shorter than the column width are padded with blanks unless there is no other printing on that line.

If `LEFTRIGHT` justification is specified, new lines are no longer preserved and lines are filled in up to the column edge. Blank lines in the text are still preserved, however, the following shows the effect of `LEFTRIGHT` justification:

    PRINT string WIDTH 45 LEFTRIGHT OVERFLOW INTO temp,
    NEWLINE;
    WHILE (temp != "")
          PRINT temp WIDTH 45 LEFTRIGHT OVERFLOW INTO temp,
    NEWLINE;
    END;

The output is:

    'I am sure you will find the hippopotamus qui
    te satisfactory, sir,' the trainer said, smiling
    ingratiatingly.

    At this point the creature rolled over, showe
    ring us with mud. 'Ah, yes ... er, well, I su
    ppose so,' Johnson replied, brushing vaguely
    at his trouser legs.

Note

Words are still split in the middle. This may be overcome by setting the system variable `WRAPMARGIN` to `PAGEWIDTH`; setting it to 0 will split words again.

The following statements:

    LET WRAPMARGIN = PAGEWIDTH;
    PRINT string WIDTH 45 OVERFLOW INTO temp, NEWLINE;
    WHILE (temp != "")
          PRINT temp WIDTH 45 OVERFLOW INTO temp, NEWLINE;
    END;

produce:

    'I am sure you will find the hippopotamus
    quite satisfactory, sir,' the trainer said, smiling
    ingratiatingly.

    At this point the creature rolled over,
    showering us with mud. 'Ah, yes ... er, well, I
    suppose so,' Johnson replied, brushing
    vaguely at his trouser legs.

Finally, with both `WRAPMARGIN` and `LEFTRIGHT` justification:

    LET WRAPMARGIN = PAGEWIDTH;
    PRINT string WIDTH 45 LEFTRIGHT OVERFLOW INTO temp,NEWLINE;
    WHILE (temp != "")
          PRINT temp WIDTH 45 LEFTRIGHT OVERFLOW INTO temp,NEWLINE;
    END;

The output is:

    'I am sure you will find the hippopotamus
    quite satisfactory, sir,' the trainer said,
    smiling ingratiatingly.

    At this point the creature rolled over,
    showering us with mud. 'Ah, yes ... er, well,
    I suppose so,' Johnson replied, brushing
    vaguely at his trouser legs.

### 7.11.7 Justifying a Group of Items with CONCAT

Quite often the need arises to justify several items as a group, rather than individually. For example, suppose a name is stored as three attributes, `first`, `middle` and `last`, and the full name must be right-justified on the line with only a single space between each part of the name. This is impossible to do without assembling the components of the name into a larger group, because names vary greatly in length.

The solution is to use the keyword `CONCAT` to concatenate several expressions into a single one, and justify that. The `CONCAT` operator can be used in any expression and in `PRINT` statements. The statement that prints the names mentioned above properly (assuming a 65-character line length) are:

    PRINT first CONCAT " " CONCAT middle CONCAT " " CONCAT last
          WIDTH 65 RIGHT, NEWLINE;

### 7.11.8 Printing Data from a File

The `FILE` command allows data in a file to be included in the report output. The contents of the file are printed "as is''.

For example, if the file company_address contains the following:

    Empress Software, Inc.
    3100 Steeles Ave. East
    Markham, Ontario

Then, the contents of that file can be included in a report, as shown in the following script:

    SELECT name, salary, job_descrip FROM personnel;
    FILE "company_address";
    NEWLINE 2;
    PRINT "Employee Summary" WIDTH 60 CENTER;
    NEWLINE 2;
    PRINT COL 10, "Name", COL 30, "Salary", COL 40, "Job
         Description";
         .
         .
         .

The report would look like this:

    Empress Software, Inc.
    3100 Steeles Ave. East
    Markham, Ontario

                    Employee Summary

        Name              Salary              Job Description

### 7.11.9 Tabs

Tabs may be set by a statement of the form:

`TABSET integer {integer} [;]`

where the `integers` are the column numbers the tabs are to be set at. `TAB` may then be used in a `PRINT` statement to move to the next tabstop; `TAB` followed by a number moves to that tabstop if it is set.

For example, the statement:

    TABSET 10 20;
    PRINT TAB, "First", TAB, "Second";

will print "`First`'' starting in column 10 and "`Second`'' starting in column 20.

The statement:

    TABSET 10 20 30 40;
    PRINT TAB 3, "First";

will print "`First`'' starting in column 30.

The statement:

`TABCLEAR [;]`

clears all previously set tabs.

### 7.11.10 Precision

Floating point numbers may have their precision specified in a `PRINT` statement by the keyword `PRECISION`, followed by an expression giving the number of decimal places desired. For example, the statement:

`PRINT 1.234567E2 PRECISION 3;`

produces:

`1.235e+02`

Values are rounded where necessary.

### 7.11.11 Picture Formats

The precise formats in which numeric and date data are printed may be specified by means of a picture. Pictures allow quite complex re-formatting of data values, and are composed of a number of letters or digits enclosed in quotes, preceded by the keyword `PICTURE` (which may be abbreviated to `PIC`). For example, if the current date is April 12, 1992, the statement:

`PRINT today PICTURE "dd MM yy";`

produces:

`12 04 92`

For a numeric example, the statement:

`PRINT pagenumber PICTURE "9999";`

prints the page number for page 12 in the format:

`0012`

Note that date picture formats may only be used on date attributes or variables. Similarly, numeric picture formats may only be used on numeric attributes or variables.

The following table contains the number elements used to make up a picture specification, and an explanation of what each one does.

Table 7-2: Number Elements

Symbol | Meaning
`*` | Print a digit in this position. If the digit is a leading zero, print "*" instead. If in a trailing position after a decimal point, print "0".
`9` | Print a digit in this position. If the digit is a leading zero, print "0". If in a trailing position after a decimal point, print "0".
`Z` | Print a digit in this position. If the digit is a leading zero, print a space instead. If in a trailing position after a decimal point, print "0".
`F` | Print a digit in this position. If the digit is a leading zero, print nothing, but save one space in an internal buffer. If in a trailing position after a decimal point, print "0". Saved spaces can be output with the G symbol described under "Padding With spaces" below.

Values that are too large for the picture will be rounded.

The following table contains the sign elements used to make up a picture specification, and an explanation of what each one does.

Table 7-3: Sign Elements

Symbol | Meaning
`S` | If the value to be printed is negative, print "-" at this position; if it is positive, print "+".
`-` | If the value to be printed is negative, print "-" at this position; if it is positive, print a space.
`N` | If the value to be printed is negative, print "-" at this position; if it is positive, print nothing, but save one space in an internal buffer.
`picture|picture` | If the value to be printed is positive, format it according to the first picture; if it is negative, format it according to the second picture. There may only be one "|" per picture.

The following table contains the decimal point elements used to make up a picture specification, and an explanation of what it does.

Table 7-4: Decimal Elements

Symbol | Meaning
`V` | Position of the value's decimal point. If no "V" is included in the picture, the decimal point is assumed to be at the extreme right of the picture. There may only be one "V" per picture. This symbol does not actually produce any output. Typically to print a decimal point use the following syntax: "V".

The following table contains the padding elements used to make up a picture specification, and an explanation of what each one does.

Table 7-5: Padding Elements

Symbol | Meaning
`G` | Output all the saved spaces from the internal buffer, starting at this position. Of no "G" is specified in a picture, any saved spaces are discarded. There may only be one "G" per picture.

The following table contains the character elements used to make up a picture specification, and an explanation of what it does.

Table 7-6: Character Elements

Symbol | Meaning
`@character` | Print the character following the "@" sign in this position, but do not print it if the position is an empty space. If the character would normally be a special character, its special meaning is lost.
`\character` | Print the character following the "\" in this position. If the character would normally be a special character, its special meaning is lost.
`character` | Print the specified character in this position.

The following table contains the date elements used to make up a picture specification, and an explanation of what each one does.

Table 7-7: Date Elements

Symbol | Meaning
`D` | Print the day in numeric form, printing as many digits as there are "D"s. If more "D"s than there are digits in the day are specified, print zeros to the left to make up the number of digits.
`d` | Print the day in numeric form, but only print as many digits as there are in the day. Do not add any leading zeros.
`W` | Print the day in alphabetic form, printing as many letters as there are "W"s. If more "W"s than there are letters in the day are specified, print blanks to the right to make up the number.
`w` | Print the day in alphabetic form, printing only as many letters as there are in the day. Do not add any trailing blanks.
`M` | Print the month in numeric form, print as many digits as there are "M"s. If more "M"s than there are digits in the month are specified, print zeros to the left to make up the number of digits.
`m` | Print the month in numeric form, but only print as many digits as there are in the month. Do not add any leading zeros.
`A` | Print the month in alphabetic form, printing as many letters as there are "A"s. If more "A"s than there are letters in the month are specified, print blanks to the right to make up the number.
`a` | Print the month in alphabetic form, but only print as many letters as there are in the month. Do not add any trailing blanks.
`Y` | Print the year in numeric form, printing as many digits as there are "Y"s. If more "Y"s than there are digits in the year are specified, print zeros to the left to make up the number of digits. Hence, for 1992, "YYY" prints "992", "YYYY" prints "1992, and "YYYYY" prints "01992"; for the year 92 AD, "YYYY" prints "0092". Dates before the year 0 are not supported.
`y` | Print the year in numeric form, but only print as many digits as there are in the year. Do not add any leading zeros. Dates before the year 0 are not supported.

To illustrate the effects of the various picture specifications, the following table shows the picture specification, the data value, and how the output appears for numeric values.

Table 7-8: Number Element Effects

Picture | Data | Output
`$**@, **9V.99` |
    224
    1224
    11224.7
         .7
    $***224.00
    $*1,224.00
    $11,224.70
    $*****0.70
`G$FF99V.99` |
      22
      04.8
     123.45
    1234.5
        $22.00
        $04.80
       $123.45
      $1234.56
`SZZZ` |
    -123
     123
    -012
     012
       -123
       +123
       - 12
       + 12
`-ZZZ` |
    -123
     123
    -012
     012
       -123
        123
       - 12
         12
`G$N999` |
    -123
     123
    $-123
     $123
`G$FFF\G` |
    225
     22
      4
    099
     $225G
      $22G
       $4G
      $99G
`-ZZV.99` |
     20.6
    -20.6
     18.23
      4.02
       20.60
      -20.60
       18.23
        4.02
`NFF` |
    -23
     23
      0.59
      6.28
     14.20
      -23
       30
        1
        6
       14
`S99VZZ` |
    -33.56
     12.23
    -05.03
    -3356
    +1223
    -0503
`Z9FV**` |
    123.0
     56.20
    -32.7
    12300
     5620
     3270
`SFF99` |
      462
    -3042
     3202
     +462
    -3042
    +3202

The default picture for printing dates is given by the Empress system variable `MSDATEPIC` which has the value "`dd aaaaaaaaa yyyy`''. The following table shows the effect of various date pictures.

Table 7-9: Date Element Effects

Picture | Actual Date | Output
`dd/mm/yy` |
    June 4 1992
    October 12 198
    4/6/92
    12/10/92
`dd/MM/yy` |
    June 4 1992
    October 12 198
    4/06/92
    12/10/92
`dd-aaa-yyyy` |
    June 4 1992
    June 4 92
    4-Jun-1992
    4-Jun-92
`DD aaa YY` |
    June 4 92
    04 Jun 92
`DD aaa YYYY` |
    June 4 92
    04 Jun 1992
`dd mmmmmmmmm yyyy` |
    June 4 1992
    4 6 1992
`dd aaaaaaaaa yyyy` |
    June 4 1992
    4 June 1992
`DD MM YYYY` |
    June 4 1992
    June 4 92
    04 06 1992
    04 06 1992
`DD AAAAAAAAA YYYY` |
    June 4 1992
    04 June 1992
`DDD, MM yyy` |
    October 12 198
    012, 10 992
`DD, AAAA yy` |
    October 12 198
    12, Oct 92
`AAAAAAAAA dd YYYY` |
    October 4 1992
    October 4 1992
`MM YY DD` |
    June 4 1992
    06 92 04
`YYMMDD` |
    June 4 1992
    920604

Note

A date may be specified with day, month and year in any order. Dates before the year 0 are not supported.

## 7.12 READ Statement

Data values may be read in from a file in Empress dump file format via a `READ` statement. The syntax for a `READ` statement is:

    READ dumpfile
           type attribute {type attribute}
           [BECOMES [CONTEXT] name]
           [derived-attribute-definition {derived-
           attribute-definition}]
           [statement {statement}]
    END [;]

The `dumpfile` is a file in Empress dump format, that is, in the format resulting from using the `DUMP` option in a Query Language `SELECT` command. It may, of course, be any operating system file in that format. `dumpfile` may be passed as a parameter to the script if declared to be of type `CHAR`. Note that if `dumpfile` is not a variable, it must be enclosed in quotes.

`type` is one of `CHAR`, `DATE`, `DECIMAL`, `FLOAT` or `INTEGER`. The `ATTRIBUTE` values are read line by line from the dump file; each line must have the exact number of values expected; the values must be separated by the character string specified by the Empress system variable `MSVALSEP` (the normal default is <Ctrl+V>, octal 26).

Since the `READ` statement is equivalent to a `SELECT` statement, it may be assigned to a context in the same manner. Similarly, `DEFINE` statements may be included to produce derived attributes from the attributes read in.

A statement is any Empress Report Writer statements; in particular, statements that refer to the records retrieved by the `READ`.

The closing `END` is used to limit the scope of the `READ` statement; any statements excluded from the block by the `END` will not recognize derived attributes or records retrieved in the block. Missing `END`s do not cause syntax errors.

See Chapter 2.4 and on reading from files in `REPORT DUMP` format. In this case no `READ` or `SELECT` statements are necessary; all data is read automatically from the file.

EXAMPLE

Given a file `sql` containing:

    SELECT job_type, ATTR 'hours', rate
           FROM rates, 'hours'
           WHERE date RANGE "28 Feb 1992" TO "5 Mar 1992"
           AND 'hours'.code = rates.code
           SORT BY job_type DUMP;

The output of the command can be placed in a file data with:

`empcmd repairs 'run from sql into "data"'`

This output can then be used in a report as in the following script. The script is similar to that used for the second report (Summary of Income by Job Type) in Chapter 8 "Examples''.

    HEADER
           NEWLINE 2;
           PRINT "Universal Automotive Ser\xc2 vices",
                  COLUMN 53, TODAY, NEWLINE 2;
    END;

    FOOTER
           NEWLINE 3;
           PRINT COLUMN 64, "Page ", pagenumber, NEWLINE 2;
    END;

    LET DATABASE = "repairs";
    LET PAGELENGTH = 30;

    READ "data"
          CHAR       job_type
          INTEGER    hours
          DECIMAL    rate

          DEFINE income = ATTR "hours" * 1.5 * rate;
          PRINT COLUMN 16,
          "SUMMARY OF INCOME BY JOB TYPE, FEB 28 - MARCH 5",
          "1992", NEWLINE 3;
           PRINT COLUMN 25, "Job Type Income", NEWLINE;
           PRINT COLUMN 25, "-------------------------",
    NEWLINE 2;
           FOR EACH GROUP OF job_type
                  PRINT COLUMN 25, job_type;
                  LET total = SUM OF income;
                  PRINT COLUMN 45, TOTAL PICTURE "$FFF9V.99"
    WIDTH 8
                  RIGHT, NEWLINE;
           END;
           PRINT COLUMN 25, "--------------------------",
    NEWLINE;
           PRINT COLUMN 25, "TOTAL", COLUMN 45, SUM OF income
           PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
           PRINT COLUMN 25, "--------------------------",
    NEWLINE
    END;

## 7.13 SELECT Statement

Data are retrieved from a database using a `SELECT` statement. In almost all cases, the `SELECT` statement will include a `WHERE` clause to restrict the records retrieved to those satisfying some particular conditions.

Please refer to the Empress SQL: Reference for a longer discussion of the Empress `SELECT` command, with many examples.

A `SELECT` statement is:

    SELECT [| DISTINCT |] | *             |
            | ALL      |  | expr {, expr} |
         FROM table [[ALIAS] aliasname]
         {, table [[ALIAS] aliasname]}
         [where_clause] [sort_clause];     [BECOMES [CONTEXT] name]
         [DEFINE statement {DEFINE statement}]
         [statement {statement}]
    END [;]

where:

`DISTINCT` | eliminates duplicate records from the output (`UNIQUE` may not be used as a synonym).
`ALL` | does not eliminate duplicate records from the output file.
`expr` | is described below. If an expression is anything other than an attribute it must be referred to later as `expression_n` for the `n`th expression (the `n`th item that is not an attribute, not the `n`th item in the list).

The records produced by a `SELECT` statement may be assigned to a named context to distinguish them from those produced by other `SELECT` statements. If a report has more than one `SELECT` statement with attribute names in common, it is necessary to use contexts so that `GROUP` and `RECORD` statements refer to records from the correct `SELECT` statement.

If a `BECOMES` [`CONTEXT`] name clause is used, the line preceding it must end in a semicolon.

The statements are any Empress Report Writer statements; in particular, statements that refer to records retrieved by the `SELECT`.

The scope of the `SELECT` statement in the script may be limited by using the closing `END`. This limits the selected records and derived attributes (defined in Chapter 7.2 under "DEFINE Statements'') to the block enclosed by the `SELECT ... END`. Missing `END`s do not cause syntax errors.

### 7.13.1 WHERE Clause

A `WHERE` clause is used to restrict the records affected by a `SELECT` command. When there is no `WHERE` clause, all records in the table(s) are affected.

A `WHERE` clause consists of boolean expressions; that is, expressions that evaluate to true or false. A complex `WHERE` clause may be built up using boolean expressions that are joined using `AND` and `OR`. Precedence in evaluating the parts of a complex `WHERE` clause is controlled using parentheses.

A `WHERE` clause can restrict records based on the values of attributes by comparing expressions, one or both of which may involve attributes. The comparison operators are described in the following table.

Table 7-10: Where Clause Comparison Operators

Operator | Comparison
`=` | Equality
`!=` | Inequality
`>` | Greater than
`>=` | Greater than or equal to
`<` | Less than
`<=` | Less than or equal to
`RANGE`... | Range between two specified values; range values are inclusive by default but may be specified to be exclusive.
`BETWEEN`... | Same as range
`LIKE` | Dual-case pattern match
`MATCH` | Dual-case pattern match
`!MATCH` | Dual-case pattern mismatch
`SMATCH` | Single-case pattern match
`!SMATCH` | Single-case pattern mismatch

A "~" may be used as a synonym for "!".

The syntax for a `WHERE` clause is:

`WHERE` |
    | b_expr             |
    | (b_expr)           |
    | b_expr OR b_expr   |
    | b_expr AND b_expr  |
    | NOT b_expr         |

If two boolean expressions are joined by an `AND`, both boolean expressions must be true in order for the whole boolean expression to be true.

If two boolean expressions are joined by an `OR`, either one or both boolean expressions must be true in order for the whole boolean expression to be true.

A `WHERE` clause may have boolean expressions of the form:

    |expr [

    |NULL
    |IS
    |[IS] NOT
    expr
    |] condition
    |
    |
    |
    |

An `expr` is:

    |attr
    |string
    |number
    |(expr)
    |CONVERT expr [TO] data type
    |CONVERT expr [TO] GENERIC data type
    |expr CONVERT TO data type
    |expr CONVERT [TO] GENERIC data type
    |expr operator expr
    |expr operator
    |operator expr
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |

where `operator` is:

`+` | Addition of numeric data
`-` | Subtraction of numeric data
`*` | Product of numeric data
`/` | Division of numeric data
`%` | Remainder after division of numeric data
`concat` | Concatenation of character data
`length` | Returns length of string, `CHAR` or `BULK` data
`day(s)` | Unary operator convert to days
`week(s)` | Unary operator convert to weeks
`etc.` | `year(s)`, `month(s)`, `hour(s)`, `minute(s)`, `minute(s)`, `seconds(s)`, `yearof`, `monthof`, `dayof`, `hourof`, `minuteof`, `secondof`

A `condition` is:

    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    | =
    | !=
    | >
    | >=
    | <
    | <=
    | expr
    |
    |
    |
    |
    |
    | LIKE
    | MATCH
    | !MATCH
    | SMATCH
    | !SMATCH
    | pattern
    |
    |
    |
    |
[ |
    | =
    | !=
    | ] NULL
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    BETWEEN expr [
    |EXCLUSIVE
    |INCLUSIVE
    |] [AND] expr [
    |
    |EXCLUSIVE
    |INCLUSIVE
    |]
    |
    |
    |
    |
    |
    RANGE expr
    |EXCLUSIVE
    |INCLUSIVE
    |] [TO] expr [
    |
    |EXCLUSIVE
    |INCLUSIVE
    |]
    |
    |
    |

`BETWEEN ... AND` is an alternative to `RANGE ...TO`. `MATCH` is a dual-case pattern match, while `SMATCH` is single-case. The ! preceding a `MATCH` keyword selects all records which do not match. `LIKE` performs a dual-case pattern match

In all the above, an expression can be replaced by a nested `SELECT` statement. Refer to the Empress SQL Reference manual for the form and restrictions that apply to nested `SELECT`s.

### 7.13.2 SORT Clause

A `sort_clause` is:

`SORT [BY] attr [` |
    | DESCENDING
    | ASCENDING
    |] {, attr[
    |
    | DESCENDING
    | ASCENDING
    |]}
    |

This sorts output by the named attributes with ascending order as the default. For a descending sort place `DESCENDING` after the appropriate attribute name(s).

If the data are to be processed in groups, the output of the `SELECT` statement must be sorted by the attributes on which the groupings will be based. Sorting may be done on any number of attributes at once, in ascending or descending order for each attribute. Ascending order is the default. All attributes on which the table is sorted must be included in the output from the command. For example:

`SELECT name FROM personnel SORT BY number`

is not allowed, as there is no way to distinguish the sorted groups.

If repair jobs are to be grouped on a daily basis in a report, and then by job number for each day, the `SELECT` statement used to retrieve the data must include the clause:

`SORT BY date, job_no;`

### 7.13.3 Pattern Matching

A pattern match is an operation that compares attribute values against some specified pattern of characters. If the attribute value matches the pattern, then that record satisfies the condition.

Pattern matching also uses special characters which allow a match to be made on subgroups of characters within an attribute. This enables phrases to be identified within an attribute value. The special characters are:

Table 7-11: Pattern Matching Character

Character | Use | Example
`?` | Matches any character in the position |
`*` | Matches zero or more occurrences of any character |
`[...]` | Matches any of a set of characters in the position | `[abc]` matches "a" or "b" or "c".
`{...}` | Matches zero or more occurrences of a fixed length pattern | `{[a-z]}` matches any string of lower case letter.
`[ - ]` | Matches a range of characters in the position | `[a-cf-i]` matches "a", "b", "c", "f", "g", "h", "i".
`[^ ]` | Matches anything but a set or range of characters in the position | `[^123]` matches anything except 1,2, or 3; `[^a-d]` matches anything except "a", "b", "c" or "d".
| | Requires a match on either side of the bar | `ab|cd` requires a match on "ab" or "cd".
`&` | Requires a match on both sides of the ampersand | `[a-z]&[^x]` matches any letter except "x".
`\` | Placed before any of the special characters "?", "*", "|", "&", "{", "}", "[", "]", and "\" causes that character to be interpreted as an ordinary character rather than a special one. |

### 7.13.4 Joins in SELECT Statements

A join is an operation involving two or more logically connected tables which links up their records on the basis of some common property. A join produces a set of attribute values depending on a specific form of the `WHERE` clause. For each pair of tables in a join, the `WHERE` clause must have a test for equality between an attribute in the first table and an attribute in the second table. The `WHERE` clause may specify additional restrictions but must at least include these join conditions.

### 7.13.5 Variables in SELECT Statements

Variables may also be used in a `SELECT` statement. The prefix `var`. must be placed before the variable name in order for its value to be substituted correctly. These variable names also cannot contain underscores.

If the variable `employee` has the current value `Jones`, then to select all Jones' records from the `loans` table, the following would be used:

`SELECT FROM loans WHERE name = 'var.employee';`

(The quotes are used because the substituted value will be a character string; a date, decimal or float variable does not need quotes.)

### 7.13.6 No Output from SELECT Statement

If a `SELECT` statement retrieves no records (i.e., the table is empty or no records satisfy the `WHERE` clauses), this is not considered an error and no error messages are printed. The rest of the report script will attempt to execute as usual. If later parts of the script attempt to use values from the `SELECT` statement, error messages will result from these statements.

## 7.14 System Variables

In addition to the other variables used in a report, there are several system variables which keep track of things like page number and margin control. Many may be used and manipulated just like ordinary variables; the rest provide status reports and may not be changed.

The system variables and their functions are shown in the table below. Their names may be written in any combination of upper and lower case.

Table 7-12: System Variables

Variable | Function
`DATABASE` | Holds the name of the primary database on which the report operates.
`LINENUMBER` | Holds the current line number (value cannot be changed).
`PAGELENGTH` | Specifies the page length in lines. The default is set by the Empress system variable `MSPAGELENGTH` whose default is 66.
`PAGESIZE` | Same as `PAGELENGTH`.
`PAGENUMBER` | Holds the current page number.
`PAGEWIDTH` | Specifies the page width in characters. The default is set by the Empress system variable `MSPAGEWIDTH` whose default is 132.
`POSITION` | Holds current column number in a line (value cannot be changed).
`RECORDLOCKED` | The number of locked records which were ignored during the last select.
`TODAY` | Holds the value of today's date unless reassigned a value by the user.
`WRAPMARGIN` | If set to `PAGEWIDTH`, words are not split in multi-line print if set output; if set to 0, words are split. The default is 0.

There are in addition, several Empress variables that affect Empress Report Writer. They are described in the table below:

Table 7-13: Empress Variables

Variable | Function
`MSDATEPIC` | Sets the default picture for printing dates. The default value is `dd aaaaaaaa yyyy`. `MSDATEPIC` also sets the year, month and date order for reading date parameters.
`MSMWNULLOK` | A flag indicating whether nulls will be printed without causing an error. The default is to allow nulls to be printed as blanks.
`MSPAGELENGTH` | Sets the default value for the Empress Report Writer system variable `PAGELENGTH`. Its default is 66.
`MSPAGEWIDTH` | Sets the default value for the Empress Report Writer system variable `PAGEWIDTH`. Its default is 132.

The Empress SQL variables cannot be set in an Empress Report Writer script. Refer to the Empress SQL: User's Guide manual for instructions on setting Empress system variables.

### 7.14.1 Specifying a Default Database

The default database used for the `SELECT` statement is stored in the system variable `DATABASE`. Any tables mentioned in a `SELECT` statement that do not have a database name prefix are assumed to be in that database.

If `DATABASE` is used it must be set before the `SELECT` statement is included in an Empress Report Writer script. `DATABASE` may be set from a `PARAMETER`, or in a `LET` statement.

For example, to make `repairs` the database, either:

    PARAMETERS
            CHAR	database;
    END;

with `repairs` as the argument when Empress Report Writer is called, or the following may be used:

`LET DATABASE = "repairs";`

### 7.14.2 Default Date Picture MSDATEPIC

The default picture for printing dates is controlled by the Empress variable `MSDATEPIC`. This specifies a date picture for printing all dates. The default value of `MSDATEPIC` is "`dd aaaaaaaa yyyy`''. That is, dates are printed as numeric day, followed by the month in alphabetic form, followed by the year; no leading zeros or trailing blanks are printed.

`MSDATEPIC` also sets the year, month and day order for reading date parameters.

Date pictures are described in detail under the `PRINT` statement in the "Picture Formats'' section. `MSDATEPIC` may be set to any of the picture formats described in that subsection to set a new default picture format for printing dates.

If Empress Report Writer is run with the `emprepwr` command, `MSDATEPIC` must be set in the operating system to be effective. Refer to the Empress SQL: User's Guide manual for details on how to set an Empress system variable in the operating system.

### 7.14.3 Page Length

The system variable `PAGELENGTH` is used to control page length. To set the page length, use a statement of the form:

`LET PAGELENGTH = expression [;]`

where:

`expression` | produces an integer giving the page length in lines. The page may be as long as you wish. The default page length is 66 lines, the normal number of lines on a standard 8 1/2 x 11 inch letter page.

Note that the page length includes any headers and footers specified. Moreover, unless you specify headers and footers no white space is left at the top and bottom of each page.

### 7.14.4 Page Width

The system variable `PAGEWIDTH` is used to set the page width, by a statement of the following form:

`LET PAGEWIDTH = expression [;]`

where:

`expression` | produces an integer giving the page width in characters (columns).

The page width may be as large as you wish. All column numbers are counted from the left edge of the page, which is column 1. Hence, setting the pagewidth to 70 gives 70 columns of output on each line.

The default page widthdefault page width is 132.

### 7.14.5 Page Numbering

Pages in a report are counted automatically by Empress Report Writer, and the system variable `PAGENUMBER` always contains the current page number. The page numbering may be set or altered at any point in a report by a `LET` statement of the form:

`LET PAGENUMBER = expression [;]`

where:

`expression` | produces an integer giving the appropriate page number, with no limit.

### 7.14.6 Blank Pages

To include a blank page with a header or footer in a report and have it counted in the page numbering in the normal way, simply ask for two new pages:

`NEWPAGE 2;`

## 7.15 WHILE Statement

`WHILE` statements provide a means of repeating one or more operations as long as a given condition is true. These have the form:

`WHILE [!]` | `expression {` |
    | AND
    | OR
    | &
    | |
    | [!] expression }
    |
    |
    |
    [DO]
    statement {statement}
`END [;]` |  |  |

The `expression` is a logical expression and must test whether or not one value is equal to (=), not equal to (!=), greater than (>), less than (<), greater than or equal to (>=), or less than or equal to (<=), another value. Parentheses may be used to group values or logical expressions.

If two logical expressions are separated by `AND` or "&'', both must be true for the loop to run. If `OR` or "|'' is used to separate logical expressions, the loop will run if either or both are true.

A "!'' placed before a logical expression reverses its sense; for example, `! val_1 = val_2` is equivalent to `val_1 ! = val_2`, and `! variable` is true if the variable has a value of zero.

The following loop prints the numbers from 1 to 10, one per line:

    LET number = 1;
    WHILE number < 11
         PRINT number, NEWLINE;
         LET number = number + 1;
    END;