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