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

    Monday, February 11, 2019 2:11 PM

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
    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 Answered "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]

    Monday, February 11, 2019 2:16 PM
  • 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?

    Monday, February 11, 2019 2:31 PM
  • Can you post your query?
    Monday, February 11, 2019 2:40 PM
    Moderator
  • You want to use AND:

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

    Monday, February 11, 2019 2:57 PM
    Moderator
  • (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

    Monday, February 11, 2019 3:03 PM
  • What is your WHERE clause?

    Monday, February 11, 2019 3:19 PM
    Moderator
  • 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)))
    Monday, February 11, 2019 3:21 PM
  • 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)))

    Monday, February 11, 2019 3:26 PM
    Moderator
  • 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
    • Edited by Beamer108 Monday, February 11, 2019 3:34 PM
    Monday, February 11, 2019 3:33 PM
  • What are the data types of three columns?

    A Fan of SSIS, SSRS and SSAS

    Monday, February 11, 2019 3:49 PM
  • they are made up of 3 types VARCHAR2, NUMBER, TIMESTAMP(3)
    Monday, February 11, 2019 3:58 PM
  • 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)))

    Monday, February 11, 2019 4:09 PM
    Moderator
  • varchar2 and timestamp(3) are an oracle (and maybe mysql) datatypes, not sql server. Are you really using tsql here? 
    Monday, February 11, 2019 4:15 PM
  • 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
    Monday, February 11, 2019 4:18 PM
  • 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

    Monday, February 11, 2019 5:18 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
    Monday, February 11, 2019 5:38 PM