Many Boolean expressions in SQL Case

user_15582492 0
Viewed 83

I have a question concerning boolean expressions in a SQL Case block. I´m using an Oracle database. The column´s name will be created by the Alias name which was declared at the end of each Case Block.

Is there a way to reduce this SQL Case code example?

case oracle sql
2 Answers

You may get rid of the repeated predicates by calculating the condition once in CTE and reuse it in the query as follows

with cte as
(select
    CASE
        WHEN {complex condition No 1}
           THEN 1
        WHEN {complex condition No 2}
           THEN 2
        WHEN {complex condition No 3}
           THEN 3
    END condition_id,
 a.*
 from my_example_table a
)
select
    value_of_column_47, 
    CASE
        WHEN condition_id = 1 
           THEN 6
        WHEN condition_id = 2 
           THEN value_of_column_18
        WHEN condition_id = 3 
           THEN value_of_column_82
    END my_first_reached_value,
...
 from cte
user_48081220

You can use write three query three condition and use union all to combine those like below:

SELECT
    value_of_column_55,
    value_of_column_47, 
    6 my_first_reached_value,
    44 my_second_reached_value,
    77 my_third_reached_value

FROM my_example_table
WHERE      column_a = 1 AND column_b = 2
UNION ALL

SELECT
    value_of_column_55,
    value_of_column_47, 
    value_of_column_18 my_first_reached_value,
    value_of_column_66 my_second_reached_value,
    value_of_column_89 my_third_reached_value

FROM my_example_table
WHERE      column_a = 1 AND coulmn_b = 2 AND column_c = 8

UNION ALL

SELECT
    value_of_column_55,
    value_of_column_47, 
    value_of_column_82 my_first_reached_value,
    'Some_hardcodedtext' my_second_reached_value,
    'Some_hardcodedtext_again' my_third_reached_value

FROM my_example_table
WHERE      column_a = 1 AND column_b = 2 AND (column_c = 8 or column_r = 22)

But I am afraid that the conditions are properly mentioned. Your second and third condition is unreachable since there is no way that the first condition is false and second or third condition is true.

First condition is true when second condition is true and first and second condition is both true when third condition is true.

Please share sample input and desired output so that we can help you to construct proper conditions.

user_86516010
Related