title: SQL: Optimize query with multiple CASE statements
body: <p>I have a query with many CASE statements that runs for a very long time due to the number of rows. In my research I have not found a solution yet. Is there a way to write the CASE statements more efficiently and with better performance?</p>
  <p>database: Oracle</p>
  <p>table_a<br>
  table<br>
  table_y <br>
  are all the same table where I SELECT from.</p>
  <p>example data</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>contract_number</th>
  <th>product_description</th>
  <th>product</th>
  <th>damagenumber</th>
  <th>date</th>
  <th>internalname</th>
  <th>payment</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Product T-Shirt</td>
  <td>product_name</td>
  <td>111</td>
  <td>20210101</td>
  <td>Web</td>
  <td>30.20</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Product T-Shirt</td>
  <td>product_name</td>
  <td>222</td>
  <td>20210202</td>
  <td>Web</td>
  <td>19.38</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Product Hoodie</td>
  <td>product_name2</td>
  <td>333</td>
  <td>20210215</td>
  <td>Store</td>
  <td>20.49</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Product Hoodie</td>
  <td>product_name2</td>
  <td>334</td>
  <td>20210302</td>
  <td>Store</td>
  <td>15.99</td>
  </tr>
  <tr>
  <td>5</td>
  <td>Product Hoodie</td>
  <td>product_name2</td>
  <td>123</td>
  <td>20210120</td>
  <td>Telephone</td>
  <td>99.99</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>SELECT
      contract_number,
      product_description,
      product,
      CASE
          WHEN ( x.produkt = 'product_name'
                 AND (
              SELECT
                  COUNT(DISTINCT damagenumber)
              FROM
                  table z
              WHERE
                  date BETWEEN add_months(trunc(sysdate), - 6) AND sysdate
                  AND internalname &lt;&gt; 'CONDITION'
                  AND x.contract_number = z.contract_number
              GROUP BY
                  z.contract_number 
          ) = 1
                 AND (
              SELECT
                  SUM(y.payment)
              FROM
                  table_y y
              WHERE
                  date BETWEEN add_months(trunc(sysdate), - 6) AND sysdate
                  AND internalname &lt;&gt; 'CONDITION'
                  AND x.contract_number = y.contract_number
          ) &gt; 1500 ) THEN
              (
                  SELECT
                      COUNT(DISTINCT damagenumber)
              FROM
                  table z
              WHERE
                  date BETWEEN add_months(trunc(sysdate), - 6) AND sysdate
                  AND internalname &lt;&gt; 'CONDITION'
                  AND x.contract_number = z.contract_number
              GROUP BY
                  z.contract_number 
              )
              ELSE
              0
              END) AS count_numbers,
  FROM
      table_a x
  GROUP BY
      x.contract_number,
      x.product_description,
      x.product;
  </code></pre>
  <p>The above is a simplified example. I have a lot of WHEN conditions in my query.</p>
  <p>Thanks in advance</p>