NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
I-2
Considerations—IF/THEN/ELSE
The conditional expression has the same form as an SQL search condition, except
that it cannot include subqueries. It can include any form of column name (for
example, COL 3 > 5 or EMPNUM = 228). Numeric expressions within the
conditional expression cannot use the AVG, COUNT, MAX, MIN, or SUM
functions but can include column identifiers and report writer functions. (See Search
Conditions on page S-5 or Expressions on page E-23 for further details.)
THEN ( print-list )
specifies what to print if the condition is true.
ELSE ( print-list )
specifies what to print if the condition is false. If you omit the ELSE clause, report
writer prints blanks when the condition is false.
print-list
is a list of items to print and optional formats for the items. It is the same as
print-list for the DETAIL command, except that the HEADING, NOHEAD,
NAME, SKIP, PAGE, and NEED clauses are not allowed. See DETAIL Command
on page D-43 for more information.
Considerations—IF/THEN/ELSE
•
No default heading
If you want a heading for an IF/THEN/ELSE print list, you must specify one with
the HEADING clause.
•
Width of printed output
The space required to print the result of an IF/THEN/ELSE is the length of the
longest of the two print lists. The report writer pads the shorter list with blanks to
the length of the longer list.
•
No subtotals or totals
You cannot subtotal or total the values in an IF/THEN/ELSE column, because these
columns contain character values. See the NonStop SQL/MP Report Writer Guide
for a technique you can use to print conditional values in a column that can be
totaled and subtotaled.
Examples—IF/THEN/ELSE
•
Use IF/THEN/ELSE to flag an invalid job code:
S> DETAIL EMPNUM, LAST_NAME, IF JOBCODE > 0 THEN (JOBCODE)
+> ELSE ("***");