How to output different values in a single record

Question

I have a value in a string form. The field name is CF$_LMWEQUIMENT and the output is a single string separated by ^. Here is an example:

^All_Equipm^Consulting^DITCHCLEANER^Friction_M^

I need to output the real values this way of the the database value like this:

All Equipment, Consulting, Ditch Cleaner, Friction Management. 

I have this query to get CF$_LMWEQUIMENT

SELECT T1.OPPORTUNITY_NO AS BO_NUMBER,
       T2.CF$_LMWEQUIMENT AS Equiment_Group,
       T1.STATE AS Status
FROM BUSINESS_OPPORTUNITY T1 JOIN 
BUSINESS_OPPORTUNITY_CFT T2 on (T1.OBJKEY = T2.ROWKEY)

I have the API that can transform to the real value, for instance:

select custom_field_enum_values_api.Get_Client_Value('EQUIGROLMW','DITCHCLEANER') 
from dual;

The output from the API will be Ditch Cleaner. However, my goal is to have all the values in a single string and cell such as I explained in my previous paragraph -> All Equipment, Consulting, Ditch Cleaner, Friction Management.

You can notice in the picture. The field Equipment_Group has the string separated by (^)

custom_field_enum_values_api.Get_Client_Value('EQUIGROLMW','DITCHCLEANER')

But the API is only allowing me to pass two parameters: the first one is table_name and the second one is the database_value the output from the API is the real name, which is the value that I am looking for. I want my output to be similar to the picture in one cell such as Equiment_group field but I want the values output from the API in a single cell.

Answer 1

So there are extra two steps necessary:

1. Split the CF$_LMWEQUIMENT string into distinct tokens which can be passed to the translation function.
2. Concatenate the output from the translation function into a single string.

We can use REGEXP_SUBSTR() to achieve the first step and LISTAGG() to do the second. Here is a solution (with a working demo on db<>fiddle ).

select t1.opportunity_no as bo_number,
        listagg(
         custom_field_enum_values_api.get_client_value('EQUIGROLMW',regexp_substr(cf$_lmwequiment,'[^\^]+', 1, level) ) 
         , ',' ) within group (order by level)
          as equiment_group,
       t1.state as status
from business_opportunity t1 
join business_opportunity_cft t2 on (t1.objkey = t2.rowkey)
connect by regexp_substr(cf$_lmwequiment,'[^\^]+', 1, level) is not null
group by t1.opportunity_no, t1.state
/

Once again, code like this demonstrates that the wages of shonky data modelling is shocking SQL. That's why people like me always bang on about the importance of normalisation.

---

My solution only addresses a scenario where's there's one row of data to be selected. It's even more complicated when multiple rows are involved. I don't have time to do a demo for that right now, but please comment me if this doesn't work for you and I'll pick this up later.