CODEWITHSUNDEEP
sqlsql-serversql-server-2008
satyajit
satyajit

Reputation: 2700

Filtering NULL values in CASE statement

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

Upvotes: 2

Views: 7714

Answers (2)

Bohemian
Bohemian

Reputation: 425003

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:

...
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.

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.

Upvotes: 3

Guffa
Guffa

Reputation: 700302

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)

Upvotes: 3

Related Questions