case语句返回缺少括号的
将其更改为包含在where子句中,但得到了相同的错误
select tbraccd_pidm, tbraccd_term_code
from tbraccd
where (tbraccd_term_code = case tbraccd_term_code
when (substr(tbraccd_term_code,5,1) = '3')
then tbraccd_term_code IN ('201931', '201941')
else tbraccd_term_code
end)
您的查询乍一看是错误的。如果我理解你的意思,那么它会是这样的:
select tbraccd_pidm, tbraccd_term_code
from tbraccd
where tbraccd_term_code = case when substr(tbraccd_term_code, 5, 1) = '3' then ('201931', '201941')
else tbraccd_term_code
end
尽管它看起来更漂亮,但它会失败,原因与你的完全相同,没有区别。为什么?因为你不能那样使用IN;CASE不允许。如果是例如
case when substr(tbraccd_term_code, 5, 1) = '3' then '201931'
那就可以了。但是,很明显,你需要两个值。
我没有你的表格和数据,所以我将切换到Scott的EMP和DEPT表格样本,试图说明你可能会尝试做什么。
首先,一个模拟您的查询。SQL*Plus将显示它的错误(注意第5行下面的星号*
符号(:
SQL> select empno, ename, job
2 from emp e
3 where e.deptno in case when substr(e.ename, 1, 1) = 'S' then (10, 20)
4 else 30
5 end;
where e.deptno in case when substr(e.ename, 1, 1) = 'S' then (10, 20)
*
ERROR at line 3:
ORA-00907: missing right parenthesis
如何重写它以使其工作?例如,像这样:
SQL> select e.empno, e.ename, e.job
2 from emp e
3 where e.deptno in (select e1.deptno from emp e1
4 where e1.deptno in (10, 20)
5 and substr(e.ename, 1, 1) = 'S' --> this is the 1st CASE's WHEN
6 )
7 or ( e.deptno = 30
8 and substr(e.ename, 1, 1) <> 'S' --> 2nd CASE's WHEN
9 );
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7788 SCOTT ANALYST
7844 TURNER SALESMAN
7900 JAMES CLERK
8 rows selected.
SQL>
尝试此查询
SELECT TBRACCD_PIDM, TBRACCD_TERM_CODE
FROM TBRACCD
WHERE TBRACCD_TERM_CODE =
CASE TBRACCD_TERM_CODE
WHEN SUBSTR (TBRACCD_TERM_CODE, 5, 1) = '3'
THEN TBRACCD_TERM_CODE IN ('201931', '201941')
ELSE TBRACCD_TERM_CODE
END