Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

CASE/DECODE

The CASE function allows you to use conditional logic pretty much anyplace you can use an expression. CASE is flexible and efficient and comes in two forms. They look like this:

CASE expr
WHEN value1 THEN result1
  [WHEN value2 THEN result2]... [ELSE resultn]
END

CASE
WHEN condition1 THEN result1
   [WHEN condition2 THEN result2]... [ELSE  resultn]
END

Let's start with an example that translates succinct codes into more descriptive strings. The titles table includes a contract column that allows entries of either 0 or 1 to indicate the contract status. But 0 and 1 have no intrinsic meaning for contracts. Looking at a report, you'd be unclear which books were signed up and which were not. CASE examines the contract value and assigns different ...