Oracle SQL Where Case错误

Question

我有一个我正在尝试运行的查询，问题是我一直收到“缺少关键字”的通知。我正在尝试生成一个查询，该查询将根据一个代数计算的值来计算系统日期，如果它们相等，则Where语句将根据case的" then“部分后的值进行搜索。有人能帮个忙吗？

WHERE
     CASE 

                WHEN (SYSDATE) = (42192+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42192*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-01''
                WHEN (SYSDATE) = (42193+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42193*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-02''
                WHEN (SYSDATE) = (42194+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42194*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-03''
                WHEN (SYSDATE) = (42195+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42195*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-04''
                WHEN (SYSDATE) = (42198+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42198*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-05''
                WHEN (SYSDATE) = (42199+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42199*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-06''
                WHEN (SYSDATE) = (42200+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42200*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-07''
                WHEN (SYSDATE) = (42201+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42201*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-08''
                WHEN (SYSDATE) = (42202+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42202*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-09''
                WHEN (SYSDATE) = (42205+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42205*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-10''
                WHEN (SYSDATE) = (42206+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42206*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-11''
                WHEN (SYSDATE) = (42207+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42207*52)/365)/4)))  THEN' THE.DESCRIPTION = 'TEST-12''
                WHEN (SYSDATE) = (42208+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42208*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-13''
                WHEN (SYSDATE) = (42209+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42209*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-14''
                WHEN (SYSDATE) = (42212+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42212*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-15''
                WHEN (SYSDATE) = (42213+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42213*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-16''
                WHEN (SYSDATE) = (42214+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42214*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-17''
                WHEN (SYSDATE) = (42215+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42215*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-18''
                WHEN (SYSDATE) = (42216+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42216*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-19''
                WHEN (SYSDATE) = (42219+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42219*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-20''
                WHEN (SYSDATE) = (42220+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42220*52)/365)/4)))  THEN 'THE.DESCRIPTION = 'TEST-21''

                END

)；

Answer 1

我认为应该使用子查询来选择case-when-then中要查找的值，然后在主查询中执行WHERE语句。示例：

SELECT * FROM suppliers WHERE office_name = (
SELECT 
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
FROM suppliers;
);

Answer 2

您必须将整个case表达式结果与某些内容进行比较，不能在每个then中进行比较。你的引文也被弄乱了，这无助于理解你在做什么。但是看起来你想要更像这样的东西：

WHERE
  THE.DESCRIPTION = CASE 
    WHEN (SYSDATE) = (42192+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42192*52)/365)/4)))  THEN 'TEST-01'
    WHEN (SYSDATE) = (42193+30*(TRUNC((((SYSDATE)*52)/365)/4) - (((42193*52)/365)/4)))  THEN 'TEST-02'
    ...
  END

虽然我会颠倒when的计算，这样你最终会检查一些更简单的东西；因为sysdate*52 isn't going to work，我真的不确定如何解开这一部分。