I have a custom aggregate that is very cpu intensive and consumes a lot of data. But the aggregate only needs to be executed when certain infrequent conditions are met. I hoped I could do this:
CASE WHEN aggregate_is_needed = 1 THEN calculate_aggregate(xxx) over (partition by yyy order by zzz range between ... ) END
However, calculate_aggregate() is always evaluated and the result thrown away when the CASE condition is not met. Is there a way to short-circuit this aggregate? I'd like to stick with plain SQL and not use a procedural solution if possible.
There are a lot of threads on how SQL calls functions but I haven't seen this variant where a function is inside a CASE statement. Here are a few quick examples to show the point:
create function divide_by_zero return number is begin return 1/0; end;
1) select divide_by_zero from dual;
2) select case when 1=1 then null else divide_by_zero end from dual;
3) select case when 1=1 then null else sum(divide_by_zero) end from dual;
4) select case when 1=1 then null else sum(divide_by_zero) over () end from dual;
5) select case when dummy='X' then null else sum(divide_by_zero) over () end from dual;
#1 fails and #2 works-- as most people would expect.
#4 also works, while #3 and #5 fail. Note, in #3-5, the first CASE is always true so (in theory) the ELSE condition is not needed.
My situation is #5. How can I conditionally avoid the divide by zero?
Any suggestions would be appreciated. Thanks!