Teradata Concatenate multiple rows using XMLAGG getting issue in XmlAgg function or any equivalent logic to concatendate multiple rows

user_13574450 0
Viewed 3749

I have a table of record tried to concatenate multiple rows on group wise and i use XMLAGG function but when i try to run the query for particular group which has 2000 records, getting error message:

Select failed 9134 : Intermediate aggregate storage limit for aggregation has been exceeded during computation

SELECT 
  H.GROUP_id,
  H.Group_name,
  TRIM(
    TRAILING ',' FROM (
      XMLAGG(TRIM(COALESCE(H.Group_desc, -1) || '') ORDER BY H.LINE_NBR) (VARCHAR(7000))
    )
  ) AS Group_detail

even increased the varchar value but still having same issue

teradata-sql-assistant sql regex
2 Answers

XMLAGG() adds overhead. However, you can get a sense for how large the result set is by using:

SELECT H.GROUP_id, H.Group_name,
       SUM(LENGTH(COALESCE(H.Group_Desc, '-1'))) as total_string_length,
       COUNT(*) as cnt
FROM . . .
GROUP BY H.GROUP_id, H.Group_name
ORDER BY total_string_length DESC

You will probably find that some of the groups have total string close to or more than 7000 characters.

I'm not sure if you want to fix the data or do something else. But this should at least identify the problem.

user_11440350

The problem is that the concatenation would be repeated for every row in the dataset, you need to get the distinct Group_desc first, try this:

WITH BASE AS(
  SEL
     H.GROUP_id,
     H.Group_name,
     H.Group_desc,
     MAX(H.LINE_NBR) AS LINE_NBR 
  FROM TABLE_NAME
  GROUP BY 1,2,3
)
SELECT 
  BASE.GROUP_id,
  BASE.Group_name,
  TRIM(
    TRAILING ',' FROM (
      XMLAGG(TRIM(COALESCE(BASE.Group_desc, -1) || '') ORDER BY BASE.LINE_NBR) (VARCHAR(7000)) -- You probably won't need the varchar to be that large.
    )
  ) AS Group_detail

FROM BASE
user_105098640
Related