PICTURE Clause (DQL)

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.
datacom151
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.
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
Datacom Datadictionary
. Overriding a default edit pattern allows you to manipulate the final appearance of numeric data without affecting the decimal precision used in computation.
Dataquery
selects an edit pattern according to the following order of precedence.
  1. A PICTURE clause specified in a query.
  2. An edit pattern as specified in
    Datacom Datadictionary
    (when such an option is specified at your site).
  3. A default pattern based on the precision specified by the column definitions in
    Datacom 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
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,
Dataquery
presents the data as established in the edit pattern for that column in
Datacom Datadictionary
, or it uses the information pertaining to the column from the
Datacom 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
Datacom Datadictionary
,
Dataquery
creates its own edit pattern by displaying all digit positions and inserting a decimal point if needed. This generally results in leading zeros.
To override the
Datacom Datadictionary
edit pattern and the standard data format that
Dataquery
uses to present numeric columns,
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.
Topics discussed on this page include:
Euro Currency Symbol
You can use the euro currency symbol in EDIT PATTERNS and PICTURE clauses in
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,
    Dataquery
    handles the euro currency symbol in the edit patterns of the SQL Report Formatting for SQL queries on the DQF70 panel.
Datacom/DB
Datacom Datadictionary
Edit Patterns
When using
Datacom/DB
Datacom Datadictionary
edit patterns to display
Dataquery
currency fields, be aware that
Datacom Datadictionary
does not check the edit pattern, that is, if a terminal is capable of entering and displaying an edit pattern,
Datacom Datadictionary
accepts it.
Dataquery
handles the euro currency symbol in
Datacom 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.
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
    Datacom 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
    Dataquery
    rules for editing numeric columns. See the following sections.
Replacement Characters
The following is a list of the valid replacement characters you can include in an edit pattern. View the extended column display to determine the number of digits in the specified column.
  • 9
    Specifies that the corresponding digit position in the value of the column is to contain a decimal digit.
  • Z
    Indicates that if a leading zero appears in the corresponding position in the value of the column, a blank character replaces the zero when the value is displayed. If a nonzero digit appears in the corresponding position in the value, the digit is displayed.
  • *
    Indicates that if a leading zero appears in the corresponding position in the value of the column, an asterisk replaces it when the value is displayed. If a nonzero digit appears in the corresponding position, the digit is displayed.
Insertion Characters
Insertion characters cause the specified character to be inserted into the corresponding position of the value of the specified column. An insertion character indicates that a character is to be inserted between digit positions; it is not a digit position itself.
  • Decimal Point
    A
    period
    is the default. See the System Profile for other valid values. The character is inserted in the corresponding position of the data column.
    Dataquery
    shifts, rounds, and, if necessary, truncates decimal places of the value being edited to match the specified edit pattern.
  • Number Separator
    A
    comma
    is the default but any character is acceptable except &., *, or the decimal point. It causes the character to be inserted into the corresponding position of the value. If the edit pattern requests zero suppression, the number grouping character is also suppressed. This character is inserted only when an unsuppressed digit appears to its left.
  • Other insertion characters
    A
    slash
    causes the slash character to be inserted into the corresponding position of the value when the value is presented on a report. A
    hyphen
    causes the hyphen character to be inserted into the corresponding position of the value when the value is presented on a report.
Signs and Symbols
You can specify only one type of sign character or currency symbol in an edit pattern. You can specify a sign only in the rightmost position of the edit pattern. A currency symbol can be only in the leftmost position of the edit pattern.
  • $
    Specifies that a dollar sign currency symbol is to appear with the value when displayed. If you use this symbol more than once in succession, it becomes a floating character. A floating character specifies that the dollar sign is to appear left of the leftmost digit in the value. To specify a floating character, use multiple dollar signs in the edit pattern in each digit position through which it can float.
  • Specifies that a euro currency symbol is to appear with the value when displayed. If you use this symbol more than once in succession, it becomes a floating character. A floating character specifies that the euro symbol is to appear left of the leftmost digit in the value. To specify a floating character, use multiple euro symbols in the edit pattern in each digit position through which it can float.
  • -
    Specifies that the minus sign is to appear only if the value of the column is less than zero. If the value is greater than zero, no sign appears.
  • CR
    Specifies that the credit symbol is to appear only if the value is less than zero.
  • DB
    Specifies that the debit symbol is to appear only if the value is less than zero.
Examples
The following examples illustrate the use of a PICTURE clause. These edit patterns refer to numeric columns with a length of five digits. In the first column are sample PICTURE clauses. The other three columns show what the output looks like using the PICTURE clause in the first column and the data appearing at the top of each column.
PICTURE Clause
9876.5
0012.3-
0000.9
PIC '9999.9-'
9876.5
0012.3-
0000.9
PIC 'ZZZZ.Z-'
9876.5
12.3-
0.9
PIC 'ZZZ9.9-'
9876.5
12.3-
0.9
PIC '****.*CR'
9876.5
**12.3 CR
****.9
PIC '$$$$$.$CR'
$9876.5
$12.3 CR
$.9
PIC '$$$$9.9CR'
$9876.5
$12.3 CR
$0.9
Sample DISPLAY Query
The following sample query illustrates the use of a PICTURE clause in the DISPLAY statement.
FIND 10 CAI-SLSHST-REC ROWS      WITH SHIP-QTY > 0 DISPLAY FROM CAI-SLSHST-REC SLMN-ID      'REPRESENTATIVE'                             ORD-ID       'ORDER NUMBER'                             ITM-ID       'ITEM NUMBER'                             SHIP-QTY     'QUANTITY SHIPPED'                               PICTURE 'ZZZZZZ9-'                             UNIT-PRICE   'PRICE'                               PICTURE '$$$$$9.99-'                           DISC-PCT     'PERCENT DISCOUNT'                             PICTURE '99.9-'
  • Zs
    Replace leading zeros with blanks.
  • 9s
    Indicate that the corresponding digit position in the column is to be printed, even if it is a zero.
  • $
    Instructs
    Dataquery
    to place a dollar sign to the left of the first digit which is not 0. For UNIT-PRICE, an extra dollar sign is required to allow for a floating dollar sign even when the column is a full seven digits long.
  • -
    Indicates a signed column (determined by displaying the Extended Column Definition).
The number of positions identified in each PICTURE clause represents the maximum width of the column (plus any floating dollar sign).
Output
When you execute this DISPLAY query, the following appears as output. Notice the result of the PICTURE clause.
DISPLAY Output with PICTURE Clause
 =>    ------------------------------------------------------------------------DQED0  DATAQUERY:  COLUMN VALUE DISPLAY                      ROW 00001 PAGE 00001  -----------------------------------------------------------------------------  REPRESENTATIVE                   I4790  ORDER NUMBER                     01002  ITEM NUMBER                      A10002  QUANTITY SHIPPED                 4  PRICE                            $19.99  PERCENT DISCOUNT                 .0                      -----------------------------------------------------------------------------  <PF1> HELP          <PF2> RETURN        <PF3> PREV ROW      <PF4> NEXT ROW  <PF5> NOT USED      <PF6> STATS         <PF7> BACKWARD      <PF8> FORWARD
Sample PRINT Query
The following query demonstrates the use of specifying an edit pattern in the PRINT statement.
FIND 10 CAI-SLSHST-REC ROWS      WITH SHIP-QTY > 0 PRINT FROM CAI-SLSHST-REC SLMN-ID      'REPRESENTATIVE'                 ORD-ID       'ORDER NUMBER'                 ITM-ID       'ITEM NUMBER'                 SHIP-QTY     'QUANTITY SHIPPED'                   PICTURE 'ZZZZZZ9-'                 UNIT-PRICE   'PRICE'                   PICTURE '$$$$$9.99-'                 DISC-PCT     'PERCENT DISCOUNT'                   PICTURE '99.9-'
  • Zs
    Replace leading zeros with blanks.
  • 9s
    Indicate that the corresponding digit position in the column is to be printed, even if it is a zero.
  • $
    Instructs
    Dataquery
    to place a dollar sign to the left of the first digit which is not 0. For UNIT-PRICE, an extra dollar sign is required to allow for a floating dollar sign even when the column is a full seven digits long.
  • -
    Indicates a signed column (determined by displaying the Extended Column Definition).
The number of positions identified in each PICTURE clause represents the maximum width of the column (plus any floating dollar sign).
Output
When you execute the preceding query, the following appears as output. Notice the result of the PICTURE clause for SHIP-QTY and UNIT-PRICE. Also notice that the DISC-PCT column output does not appear on this page of the report.
PRINT Output with PICTURE Clause
 =>   01/02/2010                  CA Dataquery                            PAGE 1A 15:30:52                                                                  DETAIL     REPRESENTATIVE  ORDER NUMBER  ITEM NUMBER  QUANTITY SHIPPED     PRICE   --------------  ------------  -----------  ----------------  --------   I4790                  01002  A10002                     4    $19.99   I4790                  01002  A20000                     1   $149.99   I4790                  01002  A30000                     2    $39.99   15266                  01004  A40000                     5     $5.99   I4790                  01002  A60000                     4    $49.99   52733                  02121  C10001                    23    $21.00   H4130                  01003  H10003                   100    $12.99   H4130                  01003  H20005                   200    $17.99   H4130                  01003  H30001                   102    $14.99   H4130                  01003  H40000                   900    $15.99        -------------------------------- LAST PAGE --------------------------------- =>  <PF1>  HELP         <PF2>  RETURN       <PF3>  TOTALS ONLY     <PF4>  DETAIL  <PF5>  NO TOTALS    <PF6>  STATS        <PF7>  BACKWARD        <PF8>  FORWARD  <PF9> NOT USED      <PF10> NOT USED     <PF11> LEFT            <PF12> RIGHT