时间戳

首页 > 解决方案 > 可以使用参数定义重叠过滤器集吗？

sql-server - 可以使用参数定义重叠过滤器集吗？

问题描述

我正在创建一个报告，我希望能够使用单个参数过滤器返回不同（重叠）的数据集。我最初开始使用 CASE 语句，直到我意识到重叠的过滤器集对于 CASE 来说是有问题的。

    WHERE
        (@ACT_INCL_STATUS = CASE WHEN (DM_IAM_D_ACT_ROOT.DEFERRAL_CD IN ('01','02','') AND DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('01')) THEN 'CR'
            WHEN (DM_IAM_D_ACT_ROOT.DEFERRAL_CD IN ('01','02','') AND DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('02','03','04','60','62','63')) THEN 'OP'
            WHEN (DM_IAM_D_ACT_ROOT.DEFERRAL_CD IN ('01','02','') AND DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('01','02','03','04','60','62','63')) THEN 'CROP'
            WHEN (DM_IAM_D_ACT_ROOT.DEFERRAL_CD NOT IN ('01','02') OR DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('06','07','08','09')) THEN 'CO'
            WHEN (DM_IAM_D_ACT_ROOT.LCYCLE_CD NOT IN ('01')) THEN 'OPCO'
            ELSE 'ALL' END)

我最近的努力使用了以下内容（认为如果语句的任何部分评估为 FALSE，整个条件都将被丢弃），但它根本没有返回任何行。

想法？

WHERE
        (@ACT_INCL_STATUS = 'CR' AND (DM_IAM_D_ACT_ROOT.DEFERRAL_CD IN ('01','02','') AND DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('01')))
        AND (@ACT_INCL_STATUS = 'OP' AND (DM_IAM_D_ACT_ROOT.DEFERRAL_CD IN ('01','02','') AND DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('02','03','04','60','62','63')))
        AND (@ACT_INCL_STATUS = 'CROP' AND (DM_IAM_D_ACT_ROOT.DEFERRAL_CD IN ('01','02','') AND DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('01','02','03','04','60','62','63')))
        AND (@ACT_INCL_STATUS = 'CO' AND (DM_IAM_D_ACT_ROOT.DEFERRAL_CD NOT IN ('01','02') OR DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('06','07','08','09')))
        AND (@ACT_INCL_STATUS = 'COOP' AND (DM_IAM_D_ACT_ROOT.LCYCLE_CD IN ('02','03','04','60','62','63','06','07','08','09')))

标签： sql-serversql-server-2012

解决方案

还有另一种方法可以做到这一点——使用所谓的查找表。考虑一下你有没有这样的桌子

V    P1   P2
'CR' '01' '01' 
'CR' '02' '01' 

'OP' '01' '02' 
'OP' '01' '03' 
'OP' '01' '04' 
'OP' '01' '60' 
'OP' '01' '62' 
'OP' '01' '63' 
'OP' '02' '02' 
'OP' '02' '03' 
'OP' '02' '04' 
'OP' '02' '60' 
'OP' '02' '62' 
'OP' '02' '63' 
'OP'  ''  '02' 
'OP'  ''  '03' 
'OP'  ''  '04' 
'OP'  ''  '60' 
'OP'  ''  '62' 
'OP'  ''  '63'

所以现在你可以加入这样的事情

  SELECT COALESCE(Lookup.V,'ALL')
  FROM table
  LEFT JOIN lookup on lookup.p1 = DEFERRAL_CD and lookup.p2 = LCYCLE_CD

推荐阅读