ZSchneidi ZSchneidi - 1 month ago 11x
SQL Question

oracle how to shorten case with complex operation

I often run into the situation that I have quite complex operations that sometimes have to be processed further with a case.

It looks pretty ugly and has lots of redundant code in it if I use it the usual way.

Lets say.

case when (1+1) > 0 then (1+1) else null end
from dual

That is my current usage of case.

Lets assume that (1+1) is my complex operation that may took several lines of code. If it matches the WHEN clause then I just want exactly that return value from the clause without having to repeat myself in the THEN part.
Because that will lead to pretty ugly and blown statements.

Is there a way to write it with having the (1+1) part only a single time in the statement?


Although I usually prefer to do such work in a CTE or subquery, sometimes that makes the query even more complicated. Here is a "simpler" method:

select nullif(greatest(1 + 1, 0), 0)
from dual;

The greatest() will return 0 for 0 or NULL values (equivalent to the condition in the case. The nullif() returns NULL for 0 or negative values.