Problem solve Get help with specific problems with your technologies, process and projects.

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"

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

The searched-when-clause syntax is:

     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:

   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

This was last published in September 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.