PL SQL - Case When语句在If语句中?

Question

我需要返回一个字段"AMOUNT"这是在两个不同的列上计算的。

最初是这样的(我知道我可以有OR语句，但这样读起来更容易):

CASE WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 3
WHEN [condition AND condition] THEN 3
WHEN [condition AND condition] THEN 3
ELSE 4
END AS AMOUNT

但是，条件会根据日期列发生变化。现在我要写aIF-ELSEIF-ELSE语句中的CASE WHEN语句

IF (DATE < 01.01.2020) THEN
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4
ELSEIF (DATE >= 01.01.2020 AND DATE <01.07.2020) THEN
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4
ELSE
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4
END AS AMOUNT

它似乎不工作，所以我认为可能有语法问题。

Answer 1

CASE结构允许嵌套。所以替换IF…ELSE:

case when date < date '2020-01-01' then
case when [condition] then 1
when [condition] then 1
when [condition] then 1
when [condition] then 2
when [condition] then 2
when [condition] then 2
when [condition] then 3
when [condition] then 3
when [condition] then 3
else 4
end
when date >= date '2020-01-01' and date < date '2020-01-07'
case when [condition] then 1
when [condition] then 1
when [condition] then 1
when [condition] then 2
when [condition] then 2
when [condition] then 2
when [condition] then 3
when [condition] then 3
when [condition] then 3
else 4
end 

else ...  ;  

注意:不应该使用date作为列/变量名。虽然它不是保留字，但它是数据类型定义。使用数据类型定义作为列/变量名是糟糕的做法。此外，我将隐式日期字符串转换为显式ISO标准。ISO标准不是必需的，但是你不应该依赖于隐式数据转换。