Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Case statements using previous case statements in a select

667651Mar 17 2011 — edited Mar 17 2011
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 14 2011
Added on Mar 17 2011
4 comments
713 views