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'.

How will I get this to work?说如果参数为“Active”，则返回符合以下条件的记录？

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]))

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

Answer 3

为什么不添加一个 OR 条件呢？喜欢

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

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