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 first published in September 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close