SQL: Optimize query with multiple CASE statements

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?

database: Oracle

table_a
table
table_y
are all the same table where I SELECT from.

example data

contract_number	product_description	product	damagenumber	date	internalname	payment
1	Product T-Shirt	product_name	111	20210101	Web	30.20
2	Product T-Shirt	product_name	222	20210202	Web	19.38
3	Product Hoodie	product_name2	333	20210215	Store	20.49
3	Product Hoodie	product_name2	334	20210302	Store	15.99
5	Product Hoodie	product_name2	123	20210120	Telephone	99.99

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 <> '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 <> 'CONDITION'
                AND x.contract_number = y.contract_number
        ) > 1500 ) THEN
            (
                SELECT
                    COUNT(DISTINCT damagenumber)
            FROM
                table z
            WHERE
                date BETWEEN add_months(trunc(sysdate), - 6) AND sysdate
                AND internalname <> '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;

The above is a simplified example. I have a lot of WHEN conditions in my query.

Thanks in advance

Answer

maybe this help

with tabletemp as (
    SELECT z.contract_number
    , COUNT(DISTINCT damagenumber) damagenumber_count
    FROM table z
    WHERE date BETWEEN add_months(trunc(sysdate), - 6) AND sysdate AND internalname <> 'CONDITION'
    GROUP BY z.contract_number 
), tabletemp2 as (
    SELECT y.contract_number
    , SUM(y.payment) payment_sum
    FROM table_y y
    WHERE date BETWEEN add_months(trunc(sysdate), - 6) AND sysdate AND internalname <> 'CONDITION'
    group by y.contract_number
)

SELECT
    contract_number,
    product_description,
    product,
    CASE WHEN ( x.produkt = 'product_name' AND tt.damagenumber_count = 1 AND tt2.payment_sum > 1500 ) THEN
            tt.damagenumber_count
            ELSE
            0
            END AS count_numbers,
FROM
    table_a x
    join tabletemp tt on (tt.contract_number = x.contract_number)
    join tabletemp2 tt2 on (tt2.contract_number = x.contract_number)

Maybe this query have some errors (I can’t test) but you should try this way