SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-46
Formats for Dates Stored as Binary Values
Note that you do not need to include YEAR TO DAY with CURRENT because the AS
DATE clause selects only the month, day, and year from the timestamp.
You can use the JULIANTIMESTAMP function with a TIMESTAMP column named
NEXT_MEETING:
JULIANTIMESTAMP (NEXT_MEETING) AS DATE 'MA DB2, Y4'
To get the day of the week printed as a word such as Sunday, use:
JULIANTIMESTAMP (CURRENT) AS DATE 'DA'
For a TIMESTAMP column named XX, use:
JULIANTIMESTAMP (XX) AS DATE 'DA'
If your database contains Julian timestamps in a column but you want to print them in
European format, you can use the CONVERTTIMESTAMP function. For example,
suppose that DATE_TIME is a Julian timestamp:
DATEFORMAT (CONVERTTIMESTAMP (DATE_TIME), EUROPEAN)
Formats for Dates Stored as Binary Values
If the date is stored as a binary value but not as a timestamp, you can use a mask to
enhance the display. For example, the DELIV_DATE column is defined as data type
NUMERIC(6) and contains values such as 870618, representing YYMMDD. To print
the date with the units separated by slashes, use this mask in the print item:
DELIV_DATE AS M<03/03/03>
The printed result is 03/07/18.
Conditional Printing of Items or Line Entries
By using an IF/THEN/ELSE clause, you can specify conditions for printing items or lists
of items, and you can specify alternate items to be printed if the conditions are not met.
An IF/THEN/ELSE clause can be included in a detail line, title, or footing and within a
CONCAT clause. You can also include an IF/THEN/ELSE clause in another
IF/THEN/ELSE clause.
The report defined in this example conditionally prints text depending on the
employee's salary:
>> SELECT EMPNUM, FIRST_NAME, LAST_NAME, DEPTNAME,
+> SALARY
+> FROM EMPLOYEE E, DEPT D
+> WHERE E.DEPTNUM = D.DEPTNUM
+> ORDER BY LAST_NAME;
S> DETAIL CONCAT (FIRST_NAME STRIP, SPACE 1, LAST_NAME)
+> AS A20 HEADING 'NAME',
+> DEPTNAME,
+> IF SALARY < 30000 THEN ('Evaluate')
+> ELSE (IF SALARY < 60000 THEN ('Wait 1 month')