SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
I-2
Considerations—IF/THEN/ELSE
conditional expression cannot use the AVG, COUNT, MAX, MIN, or SUM functions,
but can include column identifiers and report writer functions. (For more
information, see Search Conditions on page S-5 or Expressions on page E-21.)
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. For more information,
see DETAIL Command on page D-47.
Considerations—IF/THEN/ELSE

If you want a heading for an IF/THEN/ELSE print list, you must specify one with the
HEADING clause.

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.

You cannot subtotal or total the values in an IF/THEN/ELSE column, because
these columns contain character values. For a technique you can use to print
conditional values in a column that can be totaled and subtotaled, see the SQL/MP
Report Writer Guide.
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 ("***");

Use IF/THEN/ELSE to print a default value as blanks:
S> DETAIL ORDERNUM,
+> IF DELIV_DATE <> 0 THEN ( DELIV_DATE AS DATE * );

Use IF/THEN/ELSE to convert values to text:
S> DETAIL CUSTNUM, CUSTNAME,
+> IF CREDIT = "A1" THEN ("EXCELLENT") ELSE
+> ( IF CREDIT = "B1" THEN ("GOOD") ELSE
+> ( IF CREDIT = "C1" THEN ("FAIR")))
+> HEADING "CREDIT RATING";