Q

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


This was last published in September 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close