在CASE语句中过滤空值

I have the below query:

我有以下查询:

select 
  CASE WHEN rt.ROLE_TYPE_CD = 'SA' THEN ap.APPL_CD + ' ' + rt.ROLE_TYPE_CD
       WHEN ap.APPL_SID = 1 THEN  rt.ROLE_TYPE_CD  
       ELSE NULL
       END as dropdownName,   
  rt.role_type_sid as dropdownID,   
  rt.actv_ind as active,   
  'Application Role' as dropdownCatagory        
 from cdms_role_type rt   
 INNER JOIN CDMS_APPLICATION ap  
 ON rt.APPL_SID = ap.APPL_SID
 where role_type_cd != 'All Role Types'
 **AND (CASE WHEN rt.ROLE_TYPE_CD = 'SA' THEN ap.APPL_CD + ' ' + rt.ROLE_TYPE_CD
       WHEN ap.APPL_SID = 1 THEN  rt.ROLE_TYPE_CD END ) IS NOT NULL**

I want to avoid the last AND condition and still want to get rid of NULL values in dropdown column.How do I go for it? Please help

我想要避免最后一个和条件同时也想要消掉下拉列中的空值。我该怎么做呢?请帮助

2 个解决方案

#1

Filter out only the records that would produce a non-null value:

只过滤会产生非空值的记录:

select 
  CASE WHEN rt.ROLE_TYPE_CD = 'SA' THEN ap.APPL_CD + ' ' + rt.ROLE_TYPE_CD
       ELSE rt.ROLE_TYPE_CD
       END as dropdownName,   
  rt.role_type_sid as dropdownID,   
  rt.actv_ind as active,   
  'Application Role' as dropdownCatagory        
 from cdms_role_type rt   
 INNER JOIN CDMS_APPLICATION ap  
 ON rt.APPL_SID = ap.APPL_SID
 where role_type_cd != 'All Role Types'
   and (rt.ROLE_TYPE_CD = 'SA' or ap.APPL_SID = 1)

#2

A CASE that doesn't match any of the WHEN clauses is NULL, so just use that fact to turn you code into a simple match:

一个不匹配任何WHEN子句为空的情况，因此只需使用这个事实将代码转换为简单的匹配:

...
where role_type_cd != 'All Role Types'
AND (rt.ROLE_TYPE_CD = 'SA' OR ap.APPL_SID = 1)

There is no way to avoid the last AND, but it's fairly simple - I don't know why you'd want to avoid such a thing.

没有办法避免最后的结果，但它相当简单——我不知道为什么你要避免这样的事情。

Note: When using OR, make sure you use brackets. Because AND takes precedence, without brackets, A AND B OR C is parsed as (A AND B) OR C... not what you want.

注意:使用OR时，请确保使用括号。因为和优先，没有括号，A和B或C被解析为(A和B)或C…不是你想要的。

fyi, a CASE without an ELSE clause is equivalent to a CASE with an ELSE NULL, that's why it's NULL when is doesn't match any of the WHEN clauses.

顺便说一下，没有ELSE子句的情况等价于带有ELSE NULL的情况，这就是为什么当is不匹配任何when子句时它为NULL的原因。

#1