oracle中遇到的查询问题,求解答一下！

问题遇到的现象和发生背景

oracle中遇到的查询问题,求解答一下
题目：

使用的表：

emp表所有数据:

用代码块功能插入代码

我的语句：

select deptno,job,avgsal,avgcomm,
case avgcomm when avgcomm > 300 then '奖金不错'
    when avgcomm between 100 and 300 then '奖金一般'
    else '基本没有奖金'
    end '奖金评价'
from (select deptno,job,avg(sal) avgsal,avg(nvl(comm,0)) avgcomm
    from emp
    group by deptno,job
    order by deptno desc,avgsal desc)
/

运行结果及报错内容

我的解答思路和尝试过的方法

另一种：

select deptno,job,avg(sal) avgsal,avg(nvl(comm,0)) avgcomm,
case avgcomm when avgcomm > 300 then '奖金不错'
    when avgcomm between 100 and 300 then '奖金一般'
    else '基本没有奖金'
    end '奖金评价'
from emp
group by deptno,job
order by deptno desc,avgsal desc
/

提示错误：

SELECT A.*,
CASE
WHEN AC > 300 THEN
'奖金不错'
WHEN AC BETWEEN 100 AND 300 THEN
'奖金一般'
ELSE
'基本没有奖金'
END 员工奖金情况
FROM (SELECT DEPTNO, JOB, AVG(SAL) ASA, AVG(NVL(COMM, 0)) AC
FROM EMP
GROUP BY DEPTNO, JOB) A
ORDER BY DEPTNO DESC, ASA DESC;

 
select deptno,job,avgsal,avgcomm,
case when avgcomm > 300 then '奖金不错'
    when avgcomm between 100 and 300 then '奖金一般'
    else '基本没有奖金'
    end '奖金评价'
from (select deptno,job,avg(sal) avgsal,avg(nvl(comm,0)) avgcomm
    from emp
    group by deptno,job
    order by deptno desc,avgsal desc)a

已经解决
原因分析：case语句的用法，两种情况下写法不一样

eg.2 直接判断
case 列名 when ’aaa‘ then 'xxx'
when 'bbb' then 'xxx'
else 'xxx'
end xxx

eg.2 表达式判断
case when 列名>a then 'xxx'
when 列名>b then 'xxx'
else 'xxx'
end xxx

然后上面问题里命令的正确写法：

1.
 
select deptno,job,avgsal,avgcomm,
case when avgcomm > 300 then '奖金不错'
    when avgcomm between 100 and 300 then '奖金一般'
    else '基本没有奖金'
    end 奖金评价
from (select deptno,job,avg(sal) avgsal,avg(nvl(comm,0)) avgcomm
    from emp
    group by deptno,job
    order by deptno desc,avgsal desc)
/

2.
select deptno,job,avg(sal) avgsal,avg(nvl(comm,0)) avgcomm,
case when avg(nvl(comm,0)) > 300 then '奖金不错'
    when avg(nvl(comm,0)) between 100 and 300 then '奖金一般'
    else '基本没有奖金'
    end 奖金评价
from emp
group by deptno,job
order by deptno desc,avgsal desc
/

如果换成decode的话，应该这样写：
eg;

decode(表达式,判断值,'xxx',判断值,'xxx','xxx') 可取别名
上面几个例子中'xxx'都可以使用表达式替换

select deptno,job,avg(sal) avgsal,avg(nvl(comm,0)) avgcomm,
decode(sign(avg(nvl(comm,0))-300),1,'奖金不错',0,'奖金不错'
    ,-1,decode(sign(avg(nvl(comm,0))-100),1,'奖金一般',0,'奖金一般','基本没有奖金')) as 奖金评价
from emp
group by deptno,job
order by deptno desc,avgsal desc;