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.

APC · Accepted Answer · 2020-02-27T17:44:36.583

So there are extra two steps necessary:

Split the CF$_LMWEQUIMENT string into distinct tokens which can be passed to the translation function.
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.

How to output different values in a single record

1 Answers1