在条件IN子句中使用CASE

I have the failing statement:

我有失败的声明：

select 
    value_1, value_2
from 
    zt_Report
where 
    Division_CD in CASE WHEN @Division ='(ALL EPCD)' THEN ('Epoxy', 'MDP', 'OTG', 'PSR') ELSE @Division END 
    and [Business_CD] like CASE WHEN @Business='(All)' THEN '%' ELSE @Business END 
    and [Plant_TX] like CASE WHEN @Plant='(All)' THEN '%' ELSE @Plant+'%' END

Getting an error on the line:

在线上出错：

Where Division_CD in CASE WHEN @Division ='(ALL EPCD)' THEN ('Epoxy', 'MDP', 'OTG', 'PSR') ELSE @Division END

What essentially needs to happen is when the division parameter is passed (ALL EPCD), then the WHERE clause will only allow the divisions 'Epoxy', 'MDP', 'OTG', 'PSR', otherwise it should filter exactly on that divison, ie. passing Epoxy into Division will set the WHERE to locate only Epoxy division

基本上需要发生的是当传递除法参数时（ALL EPCD），那么WHERE子句将只允许分区'Epoxy'，'MDP'，'OTG'，'PSR'，否则它应该精确地过滤该divison ，即。通过Epoxy进入Division将设置WHERE仅定位Epoxy division

1 个解决方案

#1

That is not how case works. It returns a scalar value, not a list. So, you want to do something like this:

这不是案件的运作方式。它返回标量值，而不是列表。所以，你想做这样的事情：

Where (@Division = '(ALL EPCD)' and Division_CD IN ('Epoxy', 'MDP', 'OTG', 'PSR') or 
       Division_CD = @Division
      ) and 
      (@Business = '(All)' or Business_CD = @Business) and
      (@Plant = '(All)' or Plant_TX like @Plant + '%')

Your logic is mixing equality, like, and case in very strange (and incompatible) ways.

你的逻辑是以非常奇怪（和不兼容）的方式混合平等，喜欢和案例。

#1