SQL/MP Report Writer Guide

Customizing a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
4-47
Conditional Printing of Items or Line Entries
+> ELSE ("Postpone"))
+> HEADING "REVIEW PLAN";
S> LIST N 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>
You should consider the following points when specifying an IF/THEN/ELSE clause:
•
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 NonStop SQL/MP search condition syntax
and rules with the following exceptions:
°
You cannot include subqueries.
°
Arithmetic expressions cannot include aggregate functions or report functions.
You can specify predicates as the following example illustrates:
>> SELECT * FROM PERSNL.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 N 5 ;
LAST_NAME FIRST_NAME DIVISION
------------ ------------ --------
GREEN ROGER Research
HOWARD JERRY Research