How to maintain a running counter within an Oracle APEX Interactive grid report

Question

Env: Oracle APEX v5.1 / Oracle 12c

I have an interactive Grid report where I need to append a counter based on the previous cell value - in my example below, Order No Item is the Order No. value appended with "-1" etc.

Some rules here are:

1) when the Order No. value changes, the "Order No Item" counter needs to reset back to 1 as you can see when the value changes from 100 to 200. 2) the "Order No Item" also needs to be aware when an existing Order No is used and increase the counter to the next value for that Order No.

For instance:

Order No.   Order No Item
----------- -------------
100         100-1
100         100-2
100         100-3
200         200-1
200         200-2
100         100-4

Based on the above, I am performing this operation within an Oracle APEX Interactive Grid and am not sure what the best approach is for the above requirements.

I was thinking of storing the values within an APEX Collection in order to maintain counters.

NOTE: my IG is not based on any underlying table, it is all needs to be calculated on a row by row basis, as the user enters the data.

Yogesh Sharma Yogesh Sharma · Accepted Answer · 2020-01-14T09:36:01

You need a row_number() :

select t.*, row_number() over (partition by orderno order by ?) as OrderNoItem
from table t;

? assumes your data has default ordering that identifies sequence of data.

How to maintain a running counter within an Oracle APEX Interactive grid report

1 Answers