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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.