在where子句中使用case [英] using case in where clause

问题描述

我使用了以下代码..但它有一些错误...请帮助我

I have used following code.. but it have some erors...pls help me

SELECT        VoucherTypes.types, VoucherHead.VoucherNo, VoucherHead.VoucherDate, SUM(VoucherDetail.Debit) AS debit, SUM(VoucherDetail.Credit) AS credit,
                         VoucherHead.VoucherStatus
FROM  VoucherHead INNER JOIN VoucherTypes ON VoucherHead.VoucherType = VoucherTypes.vtypeid INNER JOIN
                         VoucherDetail ON VoucherHead.VoucherID = VoucherDetail.VoucherID
WHERE        (VoucherHead.VoucherDate >= @fromdate) AND (VoucherHead.VoucherDate <= @todate) AND (VoucherTypes.types = @vtype) AND (VoucherHead.Branchno = @branchid)and case(VoucherHead.VoucherStatus)when @val=1 then VoucherHead.VoucherStatus='true' when @val=2 then VoucherHead.VoucherStatus=false when @val=0 then VoucherHead.VoucherStatus=true or VoucherHead.VoucherStatus=false or VoucherHead.VoucherStatus is null

GROUP BY VoucherTypes.types, VoucherHead.VoucherNo, VoucherHead.VoucherDate, VoucherHead.VoucherStatus

请帮助我纠正这个...

pls help me to correct this...

推荐答案

Case语句的语法：

Syntax of Case statement:

CASE column_name
WHEN condition1 THEN result1
WHEN condition2 THEN result2
  ...
ELSE result
END

我不喜欢在查询中找不到任何 END

人们经常误解CASE语句的目的。您正在使用它作为控制流语句，但它是一个复杂的函数，它返回一个值，然后您可以将该值与其他值进行比较。在你的特定情况下，我会完全写出没有CASE的条件：

People often misunderstand the purpose of CASE statement. You are using it as a control flow statement but rather it's a complex function which returns a value and you can then compare that value to something else. In your particular case I would write the condition without CASE entirely:

WHERE
...
    AND (
        @val = 3
        OR (@val = 2 AND VoucherHead.VoucherStatus='false')
        OR (@val = 1 AND VoucherHead.VoucherStatus='true')
    )

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