语法错误 - where子句中的case [英] Syntax error - Case in where clause

问题描述

SELECT * FROM ABC WHERE IsActive = 1 AND 
CASE @RETVAL
WHEN 1 THEN (ListingType = 'P')
WHEN 2 THEN (@thisDate BETWEEN SearchStartDate AND SearchEndDate)
WHEN 3 THEN 
(
 (
    (@Country = 96 AND iCountry In (1, 2, 3, 96))
  OR
   (@Country <> 96 AND iCountry = @Country)
  )
)       
END

如何解决错误不正确的语法near =？

How to resolve error "incorrect syntax near ="?

推荐答案

您不能在where子句中使用类似的CASE语句。 CASE语句只能位于WHERE中=的一侧，因此您不能将ListingType ='P'作为例子。



你刚才需要与@RetVal变量进行OR运算。类似于：

You can't use a CASE statement like that in a where clause. The CASE statement can only be on 1 side of the = in a WHERE, so you can't have "ListingType = 'P'" for example.

You just need to do ORs with your @RetVal variable. Something like:

SELECT * 
FROM ABC 
WHERE IsActive = 1
AND 
(
  (@RetVal = 1 AND ListingType = 'P')
  OR 
  (@RetVal = 2 AND @thisDate BETWEEN Search AND EndDate)
  ...
)

这篇关于语法错误 - where子句中的case的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！