SQL/MP Query Guide

Retrieving Data: How to Write Queries

HP NonStop SQL/MP Query Guide—524488-003

1-43

Using CASE Expressions

A CASE expression evaluates a set of conditions and returns a result that depends on

which condition is true. These are some of the ways you can use CASE:

•

Decoding values

•

Evaluating multiple conditions

•

Computing aggregates based on specific conditions

•

Finding the highest value in a row

•

Converting long, narrow tables into short, wide ones

•

Ignoring the largest and smallest values in a set

Using CASE expressions, you can reduce the number of table scans, often to a single

scan.

CASE is a conditional expression, not a statement. You can use a CASE expression in

a subquery, but only in the predicate portion, not in the SELECT portion of the

statement.

The following subsections provide examples and more information on some of the

possible uses for CASE.

Decoding Values

You can use CASE expressions when you need to change the representation of data.

Typically, a CASE expression is used to decode values so that the results are more

meaningful. For example, you might want to store the value “M” but present the value

“MALE”. Using CASE, you can do this without involving the host program. CASE

expressions help you to avoid using a join against a lookup table and they can

eliminate your need to write an application program or client tool code.

This an example of a CASE expression that decodes values:

SELECT LAST_NAME, FIRST_NAME,

CASE MARITAL_STATUS

WHEN 1 THEN "SINGLE"

WHEN 2 THEN "MARRIED"

WHEN 3 THEN "DIVORCED"

ELSE NULL

END

FROM EMPLOYEE ;