32

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Kevin Pauli
  • 8,577
  • 15
  • 49
  • 70

3 Answers3

54

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.

Thorsten
  • 12,921
  • 17
  • 60
  • 79
  • Thanks! Okay I guess I oversimplified my problem. What if the columns are the result of a join, i.e. SELECT ... FROM MYTABLE M JOIN ANOTHER A ON M.ID = A.ID? – Kevin Pauli Nov 17 '09 at 21:23
  • 1
    Nevermind, I just found the answer to this join question. It's select M.*, A.*, (case ... Thanks again! – Kevin Pauli Nov 17 '09 at 21:26
8

As IronGoofy says, add the table alias.

On a different note be aware that there is a handy searched case syntax that would be suitable for your situation:

select t.*,
       case PRI_VAL
         when 1 then 'High' 
         when 2 then 'Med' 
         when 3 then 'Low' 
       end as PRIORITY 
from MYTABLE t;
David Aldridge
  • 51,479
  • 8
  • 68
  • 96
-3

Do it like this:

select e.*,
case deptno
when 30 then 'High'
when 20 then 'Medi'
when 10 then 'Low'
else 'Very Low'
end case
from emp e order by deptno desc;
Thiago Ferreira
  • 661
  • 3
  • 19
Venkat Reddy
  • 1