Case Statement - Use in where statement to eliminate nulls

Question

My first time posting so bear with me.

I have a sql that has a multiple case statement in the select statement that creates 3 separate columns.  I want to eliminate the entire row if all 3 columns are 'null'.  I assumed that would be something I add into the WHERE statement but wasn't 100% sure and how to write it.

Answer 1

Hi,

You can add into the where statement like this :

WHERE (CASE WHEN ….. END) IS NOT NULL OR (CASE WHEN ….. END)  IS NOT NULL OR (CASE WHEN ….. END) IS NOT NULL

---

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

 [User Page]     [MSDN Page]     [Blog]     [Linkedin]

Answer 2

Thank you. I did try that and I am still getting null rows

   ER_BSDL    OBVS_BSDL   SRC_BSDL

A    (null)          (null)              (null)

B    (null)          (null)              (null)

C    (null)          20                 (null)

D    (null)          (null)              30

E      75            (null)              (null)

F     (null)         (null)              20

G    (null)          30                 (null)

I want to exclude rows A and B.  Im wondering if its because its looking for where all null in the column vs the row?

Answer 3

Can you post your query?

Answer 4

You want to use AND:

WHERE (ER_BSDL IS NOT NULL AND OBVS_BSDL IS NOT NULL AND  SRC_BSDL IS NOT NULL)

Answer 5

(case when t3.bsdl_type = 'ER' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) and (clcl.clcl_low_svc_dt between t3.bsdl_eff_dt and t3.bsdl_term_dt) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end ) ER_BSDL,

(case when t3.bsdl_type = 'OBSV' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) and (clcl.clcl_low_svc_dt between t3.bsdl_eff_dt and t3.bsdl_term_dt)then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end ) OBSV_BSDL,

(case when t3.bsdl_type = 'SRC' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) and (clcl.clcl_low_svc_dt between t3.bsdl_eff_dt and t3.bsdl_term_dt)then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end ) SRC_BSDL

Answer 6

What is your WHERE clause?

Answer 7

and((case when t3.bsdl_type = 'ER' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end is not null) or (case when t3.bsdl_type = 'OBSV' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end is not null) or (case when t3.bsdl_type = 'SRC' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end is not null)))

Answer 8

Tom already pointed it out that you should use AND instead of OR  to check these three expression result in your where clause.

and((case when t3.bsdl_type = 'ER' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end is not null) AND (case when t3.bsdl_type = 'OBSV' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end is not null) AND (case when t3.bsdl_type = 'SRC' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end is not null)))

Answer 9

Ok thank you.. I did see that and I've tried both 'and' and 'or' but neither seem to be working as I would expect them too. 

Actually using 'And' returns no values

Answer 10

What are the data types of three columns?

---

A Fan of SSIS, SSRS and SSAS

Answer 11

they are made up of 3 types VARCHAR2, NUMBER, TIMESTAMP(3)

Answer 12

Try this:

and
NOT ((case when t3.bsdl_type = 'ER' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end
 is  null) 
 AND (case when t3.bsdl_type = 'OBSV' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end 
 is  null) 
 AND (case when t3.bsdl_type = 'SRC' and (t3.bsdl_eff_dt between d_Product_Flag_BSDL.PDBC_EFF_DT and d_Product_Flag_BSDL.PDBC_TERM_DT) then (t3.bsdl_copay_amt+t3.bsdl_dede_amt+ t3.bsdl_coin_pct) end 
 is  null)))

Answer 13

varchar2 and timestamp(3) are an oracle (and maybe mysql) datatypes, not sql server. Are you really using tsql here? 

Answer 14

Just wondering why your nulls are in parenthesis. It seems like the columns have a value of '(null)' instead of being null/ no value. If that is the case, in your where clause you will have to do the following

(Case ... End ) <> '(null)' AND (Case ... End ) <> '(null)'AND (Case ... End ) <> '(null)'

Also for readability, you may put the query in a CTE or a subquery, so you can use the column names instead of the CASE expression in the WHERE clause.

Answer 15

Did you try first to get the results and then filter the records with all NULL in those three columns?

---

A Fan of SSIS, SSRS and SSAS

Answer 16

Thank you EVERYONE for your willingness to help me so quickly. It is working.

I put the query in a subquery and used the column names and did:

where er_bsdl is not null or obsv_bsdl is not null or src_bsdl is not null

Thanks again for the ideas to get this figured out.