PICTURE Clause

Purpose

You can specify the appearance of numeric data on a report by adding a PICTURE clause to the PRINT or DISPLAY statement. An edit pattern establishes a format for the display of non-floating point numeric data. The purpose of an edit pattern is to convert numeric data into a more readable format. This format can indicate a dollar value, a minus sign, a credit or debit, and so forth.

Explanation

You can specify an edit pattern in the PRINT or DISPLAY statement in your query. When you include a PICTURE clause in one of these statements, this overrides the default edit pattern defined by Datadictionary. Overriding a default edit pattern allows you to manipulate the final appearance of numeric data without affecting the decimal precision used in computation.

CA Dataquery selects an edit pattern according to the following order of precedence.

A PICTURE clause specified in a query.
An edit pattern as specified in Datadictionary (when such an option is specified at your site).
A default pattern based on the precision specified by the column definitions in Datadictionary.

A numeric value is shifted to fit a PICTURE clause by aligning the decimal points. Truncation (dropping all digits right of the decimal) of a numeric value is permitted when the value can be rounded and truncated to fit the PICTURE clause. However, an error condition results if truncation is attempted on the integers to the left of the decimal.

When CA Dataquery processes a query, it presents the values of numeric columns or keys as specified in the PICTURE clause of the PRINT or DISPLAY statement of the query. If you do not include a PICTURE clause in the PRINT or DISPLAY statement, CA Dataquery presents the data as established in the edit pattern for that column in Datadictionary, or it uses the information pertaining to the column from the Datadictionary definition for the column, as shown in the extended column display.

If, however, you do not define an edit pattern for numeric columns to Datadictionary, CA Dataquery creates its own edit pattern by displaying all digit positions and inserting a decimal point if needed. This generally results in leading zeros.

Explanation

To override the Datadictionary edit pattern and the standard data format that CA Dataquery uses to present numeric columns, CA Dataquery provides the PICTURE clause. The PICTURE clause consists of the word PICTURE followed by a sequence of characters, called an edit pattern, which is enclosed in apostrophes (').

An edit pattern can contain several types of characters that are used to specify the way in which each digit of the numeric column appears when displayed. The types of characters which you can include in an edit pattern are replacement characters, insertion characters, sign and currency symbols.

Euro Currency Symbol

You can use the euro currency symbol in EDIT PATTERNS and PICTURE clauses in CA Dataquery currency fields, that is, the following symbol:

To use the euro symbol:

In DQL mode, the euro currency symbol can be specified in the PICTURE clauses on the PRINT statement in a query.
In SQL mode, CA Dataquery handles the euro currency symbol in the edit patterns of the SQL Report Formatting for SQL queries on the DQF70 panel.

CA Datacom/DB Datadictionary Edit Patterns

When using CA Datacom/DB Datadictionary edit patterns to display CA Dataquery currency fields, be aware that Datadictionary does not check the edit pattern, that is, if a terminal is capable of entering and displaying an edit pattern, Datadictionary accepts it. CA Dataquery handles the euro currency symbol in Datadictionary edit patterns but does not handle other nonstandard currency symbols.

►►─ DISPLAY ─┬────────┬─┬─────────────────────────────┬───────────────────────►
             └─ FROM ─┘ └─ table-name ─┬────────────┬─┘
                                       └─ (status) ─┘

 ►─┬─ key-name ────┬─┬─────────────────────────────────┬──────────────────────►
   └─ column-name ─┘ └─ 'heading1 ─┬─────────────┬─ ' ─┘
                                   └─ /heading2 ─┘

 ►─┬──────────────────────────┬───────────────────────────────────────────────►◄
   └─ PICture 'edit-pattern' ─┘

Repeat parameters once for each key or column.

►►─ PRINT ─┬────────┬─┬─────────────────────────────┬─┬─────┬─────────────────►
           └─ FROM ─┘ └─ table-name ─┬────────────┬─┘ └─ ( ─┘
                                     └─ (status) ─┘

 ►─┬─ key-name ────┬─┬─────┬─┬─────────────────────────────────┬──────────────►
   └─ column-name ─┘ └─ ) ─┘ └─ 'heading1 ─┬─────────────┬─ ' ─┘
                                           └─ /heading2 ─┘

 ►─┬──────────────────────────┬───────────────────────────────────────────────►◄
   └─ PICture 'edit-pattern' ─┘

Repeat parameters once for each key or column.

Explanation

The following explains 'edit pattern' in the preceding diagrams.

PICTURE 'edit pattern'

Specifies how data appears when displayed. Entering an edit pattern overrides the edit pattern specified in Datadictionary. (The short form for PICTURE is PIC.)

The edit pattern must appear within apostrophes ('). If you are using the PICTURE clause with a signed result of a SET statement or with a signed numeric column, the PICTURE clause should include a sign (although one will be added automatically, if needed). For example, PICTURE 'ZZZZZZ99.99-'.

The new edit pattern must conform to CA Dataquery rules for editing numeric columns. See the following sections.