Oracle SQL Where Case Error

Question

我有这个查询，我试图运行，问题是我不断得到"缺少关键字"的通知。我试图生成一个查询，将计算系统日期对一个代数计算值，如果它们是相等的，那么Where语句将搜索基于我的情况下的"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不会工作，我真的不知道如何解开这一部分。