Case statements using previous case statements in a select
667651Mar 17 2011 — edited Mar 17 2011I am having to do some migration and have been presented with a chunk of SQL that uses case statements extensively and I am not sure how to avoid this or improve.
Here is a short example of the problem:-
SELECT
num_payments_due_step_2_a,
num_payments_due_step_2_b,
num_payments_due_step_2_c,
-- there are many more here
FROM
(SELECT
-- much more code present here - below is just a snippet
case
when num_payments_due_step_2_c < 1 then num_payments_due_step_2_c + 12
else num_payments_due_step_2_c
end as num_payments_due_step_2_d,
case
when (value1 + 10) < value2 then num_payments_due_step_2_d + 1
else num_payments_due_step_2_d
end as num_payments_due_step_2_e,
case
when num_payments_due_step_2_e > 12 then 12
else num_payments_due_step_2_e
end as num_payments_due_step_2_f,
-- around 15 case statements below all referencing the previous case statement.
)
Can someone advise on the best way to re-do this, I need to be able to get the values back as a SELECT statement not as a record / other type via PL/SQL as I would have used variables and returned a record back but this is not an option. Any advise / help would be much appreciated.