Skip to Main Content
Forums

APEX

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

how to use the set column value on column change

User_NY8R9Aug 10 2022

Hi

can you provide an example on how to use the set column value on column change ?

you have a when event the doesnt seem to do anything

I have a action to display xxx and it does not fire when I change and columns

I need a way to capture a grid column popup lov change event to update another column based on an existing values in the current row/columns

thanks

Comments

598338
Sorry, deleted !
jeneesh 
SQL> select dt,cnt,sum(cnt1) over(order by dt) cnt1
  2  from(
  3  select dt,count(distinct visitor) cnt,sum(flg) cnt1
  4  from
  5   (select dt,visitor,(select decode(count(*),0,1,0)
  6                           from test
  7                           where rowid < t.rowid
  8                           and visitor = t.visitor) flg
  9   from test t)
 10  group by dt);

DT                CNT       CNT1
---------- ---------- ----------
10-01               3          3
10-02               3          4
10-03               3          5
10-04               2          5

Message was edited by: 
        jeneesh
Wrong...
450441 
WITH t1 AS (select trunc(date) date, count(distinct visitor) cnt
 from sample
group by trunc(date))
, t2 as (select trunc(t1.date) date, count(distinct t2.visitor) cnt
from sample t2, t1
 where trunc(t2.date) <= trunc(t1.date)
 group by trunc(t1.date))
select t1.date, t1.cnt visitors_per_day, t2.cnt cum_visitors
from t1, t2
where t1.date = t2.date
Message was edited by:
Dave Hemming
This was much more complicated than I first thought it would be...
611012
thanks a lot for quick response
Aketi Jyuuzou 
create table visitTable(day1,visitor) as
select '10-01','A' from dual union all
select '10-01','A' from dual union all
select '10-01','B' from dual union all
select '10-01','B' from dual union all
select '10-01','C' from dual union all
select '10-01','C' from dual union all
select '10-02','A' from dual union all
select '10-02','C' from dual union all
select '10-02','C' from dual union all
select '10-02','D' from dual union all
select '10-02','D' from dual union all
select '10-03','B' from dual union all
select '10-03','B' from dual union all
select '10-03','B' from dual union all
select '10-03','A' from dual union all
select '10-03','A' from dual union all
select '10-03','F' from dual union all
select '10-04','A' from dual union all
select '10-04','A' from dual union all
select '10-04','F' from dual;

select day1,count(distinct visitor) as cnt,
sum(sum(willSum)) over(order by day1) as cnt1
from (select day1,visitor,
      case Row_Number() over(partition by visitor order by day1)
           when 1 then 1 else 0 end as willSum
        from visitTable)
group by day1
order by day1;

DAY1   CNT  CNT1
-----  ---  ----
10-01    3     3
10-02    3     4
10-03    3     5
10-04    2     5

or

select distinct day1,count(distinct visitor) over(partition by day1) as cnt,
sum(willSum) over(order by day1) as cnt1
from (select day1,visitor,
      case Row_Number() over(partition by visitor order by day1)
           when 1 then 1 else 0 end as willSum
        from visitTable)
order by day1;

similar thread
569685

jeneesh
Nicely Done, Aketi Jyuuzou ...
572471
playing with model:
SQL> with t as (
  2  select '10-01' day1,'A' visitor from dual union all
  3  select '10-01','A' from dual union all
  4  select '10-01','B' from dual union all
  5  select '10-01','B' from dual union all
  6  select '10-01','C' from dual union all
  7  select '10-01','C' from dual union all
  8  select '10-02','A' from dual union all
  9  select '10-02','C' from dual union all
 10  select '10-02','C' from dual union all
 11  select '10-02','D' from dual union all
 12  select '10-02','D' from dual union all
 13  select '10-03','B' from dual union all
 14  select '10-03','B' from dual union all
 15  select '10-03','B' from dual union all
 16  select '10-03','A' from dual union all
 17  select '10-03','A' from dual union all
 18  select '10-03','F' from dual union all
 19  select '10-04','A' from dual union all
 20  select '10-04','A' from dual union all
 21  select '10-04','F' from dual)
 22  --
 23  select d,cnt1,cnt2 from t
 24   model
 25    return updated rows
 26    dimension by (day1 d, rownum rn)
 27    measures(visitor, 0 cnt1, 0 cnt2)
 28     (cnt1[for d in (select day1 from t), 0]=count(distinct visitor)[CV(),any],
 29      cnt2[any,0]=count(distinct visitor)[d<=CV(),any])
 30      order by 1
 31  /

D           CNT1       CNT2
----- ---------- ----------
10-01          3          3
10-02          3          4
10-03          3          5
10-04          2          5

SQL>
Message was edited by:
Volder

or a better version with "upsert all":
...
 22  --
 23  select d,cnt1,cnt2 from t
 24   model
 25    return updated rows
 26    dimension by (day1 d, rownum rn)
 27    measures(visitor, 0 cnt1, 0 cnt2)
 28    rules upsert all
 29     (cnt1[any,0]=count(distinct visitor)[CV(),any],
 30      cnt2[any,0]=count(distinct visitor)[d<=CV(),any])
 31      order by 1
 32  /

D           CNT1       CNT2
----- ---------- ----------
10-01          3          3
10-02          3          4
10-03          3          5
10-04          2          5

SQL>
1 - 7

Post Details

Added on Aug 10 2022
#foex, #foex-how-to
9 comments
426 views