How to conditionally select a column in an Oracle query

Question

I want to do something like:

select (if lookup = 8 then 08 else lookup) lookup
  , //more columns
from lookup_table
order by lookup

Unfortunately, Oracle doesn't seem to like this syntax, and I am failing to discover why or find what other function I should be using.

Basically, if the lookup value is 8, I want to get 08, otherwise I want the value of lookup. I'm sure I'm just being stupid.

Solution

You want a case statement:

select (case when lookup = 8 then 8 else lookup end) as lookup

If lookup is a character string, you probably want:

select (case when lookup = '08' then '08' else lookup end) as lookup

If lookup is an integer and you want to convert it to a string, then:

select (case when lookup = 8 then to_char(lookup, '00') else to_char(lookup, '00') end) as lookup

However, that would seem redundant to me.