0 Replies Latest reply: Nov 1, 2012 5:43 PM by TrojanSpirit RSS

    Substr Function Issue

    TrojanSpirit
      Hello,

      I have a following query

      Select '||' || RW."Description" || ':' || RW."Reason_Code"
      from TMTABLE UL, XMLTABLE('//Row' PASSING UL.TEXT
      COLUMNS
      "Event" CHAR(10) PATH 'Event',
      "Reason_Code" PATH 'Reason_Code',
      "Description" PATH 'Description'
      ) AS RW
      WHERE
      UL.ID = '[Param.1]' and
      UL.QUEUENAME = '[Param.2]' and
      RW."Event" = '[Param.3]'

      which returns me following result set

      ||A:00052
      ||B:00053
      ||C:00054
      ||D:00055
      ||E:00056

      But I want all these rows to be concatenated like this : A:00052||B:00053||C:00054||D:00055||E:00056

      So I had written following query:

      Select substr( (Select '||' || RW."Description" || ':' || RW."Reason_Code"
      from TMTABLEUL, XMLTABLE('//Row' PASSING UL.TEXT
      COLUMNS
      "Event" CHAR(10) PATH 'Event',
      "Reason_Code" PATH 'Reason_Code',
      "Description" PATH 'Description'
      ) AS RW
      WHERE
      UL.ID = '[Param.1]' and
      UL.QUEUENAME = '[Param.2]' and
      RW."Event" = '[Param.3]' ) , 3 , 2000 ) from Dual

      But it gives this error: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

      what should I do to achieve desired result?