SQL/MX Report Writer Guide

Customizing a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
4-47
Conditional Printing of Items or Line Entries
+> ELSE ('Postpone'))
+> HEADING 'REVIEW PLAN';
S> LIST NEXT 10;
NAME DEPTNAME REVIEW PLAN
-------------------- ------------ ------------
HERB ALBERT ENGLND SALES Wait 1 month
RICHARD BARTON FINANCE Evaluate
MARLENE BONNY RESEARCH Evaluate
ERIC BROWN RESEARCH Postpone
SUSAN CHAPMAN PERSONNEL Evaluate
JOHN CHOU ASIA SALES Evaluate
DINAH CLARK CORPORATE Wait 1 month
LARRY CLARK FINANCE Evaluate
MANFRED CONRAD RESEARCH Wait 1 month
STEVE COOK RESEARCH Postpone
S>
When specifying an IF/THEN/ELSE clause, consider these points :
•
The print list must be enclosed in parentheses. In the preceding example, the
ELSE print list consists of another IF/THEN/ELSE clause.
•
The report writer determines the space required for the result of the clause by
using the longest of the alternate print lists. The shorter print list result is padded
with blanks.
•
If you omit the ELSE clause, nothing is printed when the condition is not met.
•
You specify the condition by using the SQL/MX search condition syntax and rules
with these exceptions:
°
You cannot include subqueries.
°
Arithmetic expressions cannot include aggregate functions or report functions.
To specify predicates:
>> SELECT * FROM EMPLOYEE;
S> DETAIL LAST_NAME AS A12,
+> FIRST_NAME AS A12,
+> IF DEPTNUM BETWEEN 3000 AND 4000
+> THEN ('Sales')
+> ELSE (IF DEPTNUM IN (2000, 2500)
+> THEN ('Shipping')
+> ELSE ('Research') ) HEADING 'DIVISION';
S> LIST NEXT 5 ;
LAST_NAME FIRST_NAME DIVISION
------------ ------------ --------
GREEN ROGER Research
HOWARD JERRY Research
RAYMOND JANE Sales