O'Reilly logo

Practical SQL Handbook, The: Using SQL Variants, Fourth Edition by Sandra L. Emerson, Marcy Darnovsky, Judith S. Bowman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required