title: multiple conditions in a case statement duplicates lines in the table
body: <p>I'm trying to cluster the clients based on the conditions of the case statement below, everything was going fine until i've added this - AND pbs.pb_plano_assinatura != &quot;Anual&quot;- after that, it duplicates some lines and the value of the column corresponded to this case statement comes null. I know this code is a bit confusing, this just a part of a bigger query, I would love any kind of advice on how to do this kind of operations, this query involves multiple joins so i think I can't turn it into a materialized view. anyway, any help is welcomed. thank you!</p>
  <pre><code>CASE 
    WHEN ((IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &lt; date(&quot;2021-02-25&quot;) AND pbs.pb_plano_assinatura != &quot;Anual&quot;) THEN &quot;Mensal Antigo&quot;
    WHEN ((IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &gt;= date(&quot;2021-02-25&quot;) and (IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &lt; date(&quot;2021-06-02&quot;) AND pbs.pb_plano_assinatura != &quot;Anual&quot;) and pb_codigo_promocional is NULL THEN &quot;Mensal Pgto Entrada&quot;
    WHEN ((IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &gt;= date(&quot;2021-06-02&quot;) AND pbs.pb_plano_assinatura != &quot;Anual&quot;) THEN &quot;Mensal Gratuidade&quot; 
    WHEN ((IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &gt;= date(&quot;2021-02-25&quot;) and (IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &lt; date(&quot;2021-06-02&quot;) AND pbs.pb_plano_assinatura != &quot;Anual&quot;) and pb_codigo_promocional is not NULL THEN &quot;Mensal Cupom&quot;  
    WHEN (pbs.pb_plano_assinatura = &quot;Anual&quot; AND p.value &gt;= 250.0 AND p.status IN('RECEIVED' ,'CONFIRMED', 'RECEIVED_IN_CASH') AND p.originalDueDate = min(p.originalDueDate) over (partition by pbs.pb_id)) OR PBS.pb_id = 'ddcdff35-a044-5ccc-a65d-7e51692b269a' THEN &quot;Anual Entrada&quot; 
    WHEN ((IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &lt; date(&quot;2021-02-25&quot;))AND(pbs.pb_plano_assinatura = &quot;Anual&quot; AND p.value &lt; 250.0 AND p.status IN('RECEIVED' ,'CONFIRMED', 'RECEIVED_IN_CASH') AND p.originalDueDate = min(p.originalDueDate) over (partition by  pbs.pb_id)) THEN &quot;Mensal Antigo&quot;
    WHEN ((IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &gt;= date(&quot;2021-02-25&quot;)and (IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &lt; date(&quot;2021-06-02&quot;)) and pb_codigo_promocional is NULL AND(pbs.pb_plano_assinatura = &quot;Anual&quot; AND p.value &lt; 250.0 AND p.status IN('RECEIVED' ,'CONFIRMED', 'RECEIVED_IN_CASH') AND p.originalDueDate = min(p.originalDueDate) over (partition by  pbs.pb_id)) THEN &quot;Mensal Pgto Entrada&quot;
    WHEN ((IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &gt;= date(&quot;2021-06-02&quot;))AND(pbs.pb_plano_assinatura = &quot;Anual&quot; AND p.value &lt; 250.0 AND p.status IN('RECEIVED' ,'CONFIRMED', 'RECEIVED_IN_CASH') AND p.originalDueDate = min(p.originalDueDate) over (partition by  pbs.pb_id)) THEN &quot;Mensal Gratuidade&quot;
    WHEN ((IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &gt;= date(&quot;2021-02-25&quot;)AND (IF (o.order_created_at &lt; pbs.pb_dt_onboarding, date(pbs.pb_dt_primeiro_onboarding), date(pbs.pb_dt_onboarding))) &lt; date(&quot;2021-06-02&quot;)) and pb_codigo_promocional is not NULL AND(pbs.pb_plano_assinatura = &quot;Anual&quot; AND p.value &lt; 250.0 AND p.status IN('RECEIVED' ,'CONFIRMED', 'RECEIVED_IN_CASH') AND p.originalDueDate = min(p.originalDueDate) over (partition by  pbs.pb_id)) THEN &quot;Mensal Cupom&quot; 
    END as pb_safra,  
  </code></pre>