Answered by:
Case Statement - Use in where statement to eliminate nulls

-
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.
Question
Answers
-
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.
- Marked as answer by Beamer108 Monday, February 11, 2019 5:38 PM
All replies
-
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] -
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?
-
-
-
(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
-
-
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)))
-
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)))
-
-
-
-
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)))
-
-
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.
- Edited by Btuladhar Monday, February 11, 2019 4:22 PM
-
-
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.
- Marked as answer by Beamer108 Monday, February 11, 2019 5:38 PM