Oracle Apex multi select lov separated values in history table

Question

I have an Interactive grid with a multi select field - BONUS_TYPE Database column: BONUS_TYPE_ID NUMBER

Grid query is: select EMP_NAME,DESIGNATION,BONUS_TYPE_ID FROM EMP_BONUS_UPDATES;

It is a POP-UP LOV with allow_multi select option.

The select query for pop-up is: select bonus_type_name a, bonus_id from BONUS_DATA;

BONUS_DATA Table:

BONUS_TYPE  
BONUS_TYPE_NAME BONUS_ID
MILESTONE   1
REFERRAL    2

In IG values for say EMP John show as: MILESTONE:REFERRAL and for Andrew as just MILESTONE

In DB, they are saved as corresponding ID's. 1:2 and 1 respectively.

The problem is i have a trigger on the EMP_BONUS_UPDATES table which upon insert-update stores column data (old and new) values in history table.

For this specific column: BONUS_TYPE_ID data is being stored fine but i need to pull up a report.

Before multi select, the data was showing fine but now it is just showing as IDs and not the names obviously.

How can i write query to pull names with colon separators instead of id's.

HISTORY     
COLUMN  OLD NEW
BONUS_TYPE_ID   1   1:2

Instead of 1 and 1:2 , now that it's multi select, how do i show the displayvalues in the report?

--History table query

 select column, case when column ='BONUS_TYPE_ID' then (select bonus_type_name from bonus_Data where bonus_id =old)
    else old end old, 
    case when column ='BONUS_TYPE_ID' then (select bonus_type_name from bonus_Data where bonus_id =new)
    else new end new
    from BONUS_HISTORY;

Answer 1

Here's one option; read comments within code:

Sample data:

SQL> with
  2  -- table of all bonus types
  3  bonus_type (bonus_id, bonus_type_name) as
  4    (select 1, 'milestone' from dual union all
  5     select 2, 'referral'  from dual
  6    ),
  7  -- this is a table which - in BONUS_TYPE_ID - contains colon-separated ID
  8  -- values for multiple selections
  9  emp_bonus_updates (some_id, bonus_type_id) as
 10    (select 100, '1'   from dual union all    --> only 1 value has been selected
 11     select 101, '1:2' from dual              --> 2 values selected
 12    )

Actual query begins here:

 13  select x.some_id,
 14    -- aggregate bonus type names
 15    listagg(bt.bonus_type_name, ':') within group (order by null) result
 16  from bonus_type bt join
 17    -- split colon-separated values into rows so that you could join them to BONUS_TYPE IDs
 18    (select some_Id,
 19       regexp_substr(bonus_type_id, '[^:]+', 1, column_value) bti
 20     from emp_bonus_updates cross join
 21       table(cast(multiset(select level from dual
 22                           connect by level <= regexp_count(bonus_type_id, ':') + 1
 23                          ) as sys.odcinumberlist))
 24    ) x
 25    on x.bti = bt.bonus_id
 26  group by x.some_id;

   SOME_ID RESULT
---------- ------------------------------
       100 milestone
       101 milestone:referral

SQL>

---

Or, as you use Apex, another option which utilizes apex_string.split:

 13  select e.some_id,
 14    listagg(b.bonus_type_name, ':') within group (order by null) result
 15  from emp_bonus_updates e cross join table(apex_string.split(e.bonus_type_id, ':')) x
 16  join bonus_type b on b.bonus_id = x.column_value;
from emp_bonus_updates e cross join table(apex_string.split(e.bonus_type_id, ':')) x