Manipulate result set in outer query that passed from inner query

Question

Below query

SELECT CASE
     WHEN results like '%duplicate%' THEN ' xxx' || results
     ELSE results
END AS results
FROM (SELECT XMLAGG (XMLPARSE (CONTENT RESULTS || '|' WELLFORMED)
      ORDER BY RESULTS).getclobval () AS RESULTS
      FROM (SELECT DISTINCT
             ' ' || result || ' - ' || result_final AS RESULTS
           FROM myTable  WHERE ID = '123456'
));

In the inner query, if I apply where condition:

result not like '%duplicate%'

The concatenation with ' xxx' will not happen as there has no such data in "results" that passed to outer query. Without where conclusion, the concatenation happens however "results" with "Duplicate%" also displayed.

Is there any solution for the outer query to manipulate the "results" by not displaying any "Duplicate%" with the concatenation with ' xxx'?

Below is my test data

SQL> DESC mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RESULT                                             VARCHAR2(1024)
 RESULT_FINAL                                       VARCHAR2(1024)
 ID                                                 CHAR(6)

SQL> SELECT * FROM mytable;

Trx Duplicate    Trx Duplicate    123456
Insufficient Bal Insufficient Bal 123456

Expected result:

 xxx Insufficient Bal-Insufficient Bal|

Current result (with where condition)

Insufficient Bal-Insufficient Bal|

Current result (without where condition)

xxx Trx Duplicate-Trx Duplicate|Insufficient Bal-Insufficient Bal|

Answer 1

I hope this works for you. I know it is not the coolest way, but it does the job.

SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RESULT                                             VARCHAR2(1024)
 RESULT_FINAL                                       VARCHAR2(1024)
 ID                                                 CHAR(6)

SQL> col result for a40
SQL> col result_final for a40
SQL> set lines 140
SQL> select * from mytable

RESULT                                   RESULT_FINAL                             ID
---------------------------------------- ---------------------------------------- ------
Trx Duplicate                            Trx Duplicate                            123456
Insufficient Bal                         Insufficient Bal                         123456

Now, to get your desired output

SQL> with x as
  2  (SELECT XMLAGG (XMLPARSE (CONTENT RESULTS || '|' WELLFORMED)
  3        ORDER BY RESULTS).getclobval () AS RESULTS
  4        FROM (SELECT DISTINCT ' ' || result || ' - ' || result_final AS RESULTS FROM myTable )
  5  )
  6  select case when lower(results) like '%duplicate%'
  7         then
  8             case when lower(regexp_substr(results , '[^|]+', 1, 1 )) like '%duplicate%' then 'xxx'||regexp_substr(results , '[^|]+', 1, 2 )
  9                  when lower(regexp_substr(results , '[^|]+', 1, 1 )) not like '%duplicate%' then 'xxx'||regexp_substr(results , '[^|]+', 1, 1 )
 10                  when lower(regexp_substr(results , '[^|]+', 1, 1 )) like '%duplicate%' and lower(regexp_substr(results , '[^|]+', 1, 2 )) like '%duplicate%' then null
 11            end
 12        else results
 13        end
 14        as results
 15* from x
SQL> /

RESULTS
--------------------------------------------------------------------------------
xxx Insufficient Bal - Insufficient Bal

If you want the | at the end of the result, you only need to concatenate it.