[SOLVED] String concatenation after DISTINCT result selected

String concatenation after DISTINCT result selected

I have below query:

SELECT xmlagg(xmlparse(content RESULTS || '|' wellformed) ORDER BY RESULTS).getclobval() AS RESULTS 
FROM
(
  SELECT distinct ' '   || result|| ' - '   || result_final || ' xxx' as RESULTS from myTable where ID = '123456'
);

Currently the " xxx" will append at the end of each result_final, how can achieve by concatenating it to the very beginning of the final string of the query?

' xxx'|| RESULTS

With condition, above concatenation should only take place when result=x, else only RESULTS should be printed.

Solution

Move that string in front of the "result" not behind it.

Though, as additional condition should be met, use your current query (without 'xxx') as a subquery and apply condition via CASE.

With dummy sample data:

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

SQL> SELECT * FROM mytable;

R R ID
- - ------
x y 123456

SQL> SELECT CASE
  2            WHEN TO_CHAR (results) = 'x' THEN ' xxx' || results
  3            ELSE results
  4         END AS results
  5    FROM (SELECT XMLAGG (XMLPARSE (CONTENT RESULTS || '|' WELLFORMED)
  6                         ORDER BY RESULTS).getclobval () AS RESULTS
  7            FROM (SELECT DISTINCT
  8                         ' ' || result || ' - ' || result_final AS RESULTS
  9                    FROM myTable
 10                   WHERE ID = '123456'));

RESULTS
--------------------------------------------------------------------------------
 x - y|

SQL>