SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-39
Formatting Dates and Times
°
Embedded text in a mask descriptor is not printed because the digits that
surround the text are not printed. For example, "[FL'*']M<$ZZZZ9.99>"
produces $**355.67.
•
Decorations in a display format specify characters to be inserted in a print item
value depending on whether the value is positive, negative, zero, or too large for
the field.
For a general description of decorations, see Monetary Values
on page 4-32.
The following DETAIL command specifies that leading zeros in the price field are to be
suppressed and replaced by asterisks. Trailing blanks in the part description field are to
be filled with periods.
>> SELECT * FROM SALES.PARTS;
S> DETAIL PARTNUM,
+> PARTDESC AS "[FL '.'] A20",
+> PRICE AS "[FL '*'] F8.2",
+> QTY_AVAILABLE AS I5 HEADING "QTY";
S> LIST NEXT 4;
PARTNUM PARTDESC PRICE QTY
------- -------------------- -------- -----
2402 DAISY PRINTER,T1 .. **350.00 4425
2403 DAISY PRINTER,T2 .. **650.00 3312
3103 LASER PRINTER, X1 ..*4200.00 3300
3201 HARD DISK 20 MB .. **525.00 4436
S>
The PARTDESC column is defined with data type CHAR, so the trailing character is
appended to any trailing blanks in the value. If PARTDESC were a VARCHAR column,
the trailing characters would begin immediately after the last stored character.
Note that modifiers must be enclosed in square brackets, and a display format with a
modifier must be enclosed in double quotation marks.
In the next example, the price field is filled with asterisks between the dollar sign and
first digit; they replace the embedded comma and leading zeros of the value. The
result of applying this format to 500.00 is $***500.00.
PRICE AS "[FL'*'] M<$ZZ,ZZ9.99>",
Formatting Dates and Times
To format a date and time in your report, you must consider the data type of the date
and time value to be formatted. The data type is determined by the way the value is
generated or stored.