如何选择所有列，加上oracle 11g中的CASE语句的结果?

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:

我想要选择*，不需要输入所有单独的列，但是我还想包含一个带有case语句的自定义列。我试着以下:

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

3 个解决方案

#1

Add an alias for mytable like this:

为mytable添加一个别名如下:

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.

这并不依赖于任何特定的Oracle版本，也不依赖于其他数据库。

#2

As IronGoofy says, add the table alias.

如iron高飞所言，添加表别名。

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;

#3

-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;

#1