sqloracle-databaseora-00923

How do you select all columns, plus the result of a CASE statement in oracle 11g?

I want to select *, and not have to type out all individual columns, but I also want to include a custom column with a case statement. I tried the following:

select *, (case when PRI_VAL = 1 then 'High'
                when PRI_VAL = 2 then 'Med'
                when PRI_VAL = 3 then 'Low'
          end) as PRIORITY
from MYTABLE;

But it is complaining that

ORA-00923: FROM keyword not found where expected
Solution

  • Add an alias for mytable like this:

    select t.*, (case when PRI_VAL = 1 then 'High'
                when PRI_VAL = 2 then 'Med'
                when PRI_VAL = 3 then 'Low'
          end) as PRIORITY
from MYTABLE t;

    This is not dependent on any specific Oracle version, not sure about other databases.