Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

/*Logging table*/
create table t (
  /*To have a sequence*/
  id number GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1)
  , val number
  , src varchar2(100)
)
/*Logger to check what is going on here*/
create function f_test(
  p_val in number
  , p_src in varchar2
) return varchar2
as
  pragma autonomous_transaction;
begin
  insert into t(val, src)
  values (p_val, p_src);
  commit;
  
  return p_val;
end;
/
/*Log aggregation function invocation and case/where evaluation*/
with a as (
  select
    level as l
    , mod(level, 3) as grp
  from dual
  connect by level < 7
)
select
  grp
  , case
      when f_test(grp, 'CASE CONDITION') > 0
      then f_test(grp, 'CASE RESULT')
      else max(f_test(grp, 'AGG'))
    end as res_agg
from a
where f_test(grp, 'WHERE') = grp
group by grp


GRP
RES_AGG


2
2

0
0

1
1


select *
from t
order by id


ID
VAL
SRC


1
1
WHERE

2
1
AGG

3
2
WHERE

4
2
AGG

5
0
WHERE

6
0
AGG

7
1
WHERE

8
1
AGG

9
2
WHERE

10
2
AGG

11
0
WHERE

12
0
AGG

13
2
CASE CONDITION

14
2
CASE RESULT

15
0
CASE CONDITION

16
1
CASE CONDITION

17
1
CASE RESULT

ID	VAL	SRC
1	1	WHERE
2	1	AGG
3	2	WHERE
4	2	AGG
5	0	WHERE
6	0	AGG
7	1	WHERE
8	1	AGG
9	2	WHERE
10	2	AGG
11	0	WHERE
12	0	AGG
13	2	CASE CONDITION
14	2	CASE RESULT
15	0	CASE CONDITION
16	1	CASE CONDITION
17	1	CASE RESULT