Oracle Apex decoding checkbox created list of return values into display values for a report-CodePudding

Oracle Apex checkbox return values are saved in a table as colon delineated list:

01:02:03:04

And that is how they will appear in a report column, however I want to decode those values back into there display values:

Apple:Banana:Carrot:Durian

If this return values where from a select list I would use the following sql:

select display, return
from lov_list
where type = 'fruit'

However this obliviously returns an ORA-01722: invalid number error. The first approach I tried was maybe nested replace functions:

replace(replace(replace(replace(fruit_column, '01', Apple), '02', 'Banana'), '03', 'Carrot'), '04', 'Durian')

This works, but is not "dynamic" as in the future I would like to be able to add new values to the lov_list table and not have to update or add more nested replace functions especially if I now have over 10 new values. I theory what I would like to do is the following:

select replace(ft.fruit_column, ll.return, ll.display)
from fruit_table ft
left join lov_list ll
on ft.fruit_column like ('%'||ll.return||'%')

But this only works for same of the values:

Apple:02:03:04
02:Carrot 
etc.

I've looked multiple replace functions that other users have created, but I don't think they'll work for particular problem, not "dynamically" anyways, unless I'm missing something in those functions and they in fact could work, I'm not sure. Is they another approach I can take?

CodePudding user response：

You should first split colon-delimited values into rows, then join those values with table that contains fruit names, and - finally - aggregate the result back into a colon-delimited list of NAMES.

SQL> with
  2  chbox (val) as
  3    (select '01:02:03' from dual),
  4  fruits (id, name) as
  5    (select '01', 'Apple'  from dual union all
  6     select '02', 'Banana' from dual union all
  7     select '03', 'Carrot' from dual
  8    ),
  9  temp as
 10  -- first split colon-delimited value into rows
 11    (select level lvl,
 12            regexp_substr(val, '[^:] ', 1, level) id
 13     from chbox
 14     connect by level <= regexp_count(val, ':')   1
 15    )
 16  -- finally, join TEMP ID's with FRUITS; aggregate names
 17  select listagg(f.name, ':') within group (order by t.lvl) result
 18  from fruits f join temp t on t.id = f.id;

RESULT
------------------------------
Apple:Banana:Carrot

SQL>

Code you need begins at line #9 (just precede temp CTE name with the with keyword).

CodePudding user response：

I don't quite get your data model, but I'm a fan of using the apex_string package to convert delimited strings into rows.

For example

select display, return
from lov_list
where type = 'fruit'
and return in (
  select column_value
  from apex_string.split('01:02:03:04', ':')
)