Using CASE to change SELECT column values

I want to select a different set of values in the QMF report screen for some set of values in the database. If...

the value stored in the Column is "A" I want to show it as "B". How can I do this in a single SELECT query? Basically I want to put this logic in the QMF query:

If "select column_name" = "A"
   display "B"
If "select column_name" = "C"
   display "D"
etc..

You want a CASE structure, of which there are two varieties.

The searched-when-clause syntax is:

select
   case
     when column_name = 'A' then 'B'
     when column_name = 'C' then 'D'
     else null
   end as column_name
 from yourtable

The simple-when-clause syntax is:

select 
   case column_name
     when 'A' then 'B'
     when 'C' then 'D'
     else null
   end as column_name
 from yourtable

The ELSE clause is optional, and defaults to NULL, but it's a good idea to code ELSE NULL anyway. You could also assign some other expression in the ELSE clause, such as column_name, i.e. specify the same column, to leave it unchanged if it doesn't meet any of the conditions. Finally, I usually assign a column alias that is the same as the original column name, but you are free to call it anything you like, e.g. changed_column.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.