SQL Case语句在where子句中指定条件？

Question

我有以下查询语句：

    SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND
@ActiveInactive =
CASE 
WHEN 'Active' THEN (o.[orderactivedate] > o.[orderinactivedate])
WHEN 'Inactive' THEN (o.[orderactivedate] < o.[orderinactivedate]) 
END

这段代码返回：

这返回

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

我该如何让这个代码工作？如果参数是“Active”，那么如何返回符合以下标准的记录？

- user380432

---

顺便提一下...你需要在那个“Active” Then语句中关闭括号。 - DKnight

添加了闭合括号 - davmos

Answer 1

你可以选择另一种方式实现它：

SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND ((@ActiveInactive = 'Active' AND o.[orderactivedate] > o.[orderinactivedate])
OR   (@ActiveInactive = 'Inactive' AND o.[orderactivedate] < o.[orderinactivedate]))

- Dustin Laine

Answer 2

SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND
@ActiveInactive =
CASE 
WHEN (o.[orderactivedate] > o.[orderinactivedate]) then 'Active'
WHEN (o.[orderactivedate] < o.[orderinactivedate]) THEN 'Inactive'
END

- user745452

---

请使用大括号按钮来格式化代码 {}。否则，想得不错。 - gbn

Answer 3

为什么不加一个OR条件呢？就像这样：

SELECT... WHERE ...
AND ((@ActiveInactive = 'Active' AND o.[orderactivedate] > o.[orderinactivedate])  OR
    (@ActiveInactive = 'Inactive' AND o.[orderactivedate] < o.[orderinactivedate]))

- Tasio

Answer 4

你不能在CASE中使用这样的比较表达式

我建议考虑将比较表达式改为普通表达式的选项

...
AND
CASE @ActiveInactive 
WHEN 'Active' THEN DATEDIFF(day, o.[orderinactivedate], o.[orderactivedate])
WHEN 'Inactive' THEN DATEDIFF(day, o.[orderactivedate], o.[orderinactivedate])
END > 0

或者你可以使用 SIGN() 表达式创建一个计算列。

SIGN(DATEDIFF(day, o.[orderinactivedate], o.[orderactivedate])) =
              CASE WHEN @ActiveInactive WHEN 'Active' THEN 1 WHEN 'InActive' THEN -1 END

- gbn