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

Question

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

It sounds like the rows are getting too wide during an aggregation step. As a simple test, what if you decrease the `VARCHAR(7000)` to a much smaller size, like `VARCHAR(10)`? Does that still throw an error? — ravioli, Jul 10 '20 at 06:43
After reducing also it shows the same error the group desc have value of 10 to 30 character in each row — vinothbabu, Jul 10 '20 at 06:46
It's not the final CAST to VARCHAR(7000) that is the issue. How is Group_Desc defined? TRIM does not change the data type and intermediate sort keys are expanded to the full VARCHAR length. In other words, you may need to `CAST(H.Group_Desc AS VARCHAR(40))` or some other reasonable length. Also note that it's bad practice to COALESCE a string with a numeric -1 (though Teradata probably decides you meant `'-1'` and quietly fixes it under the covers). — Fred, Jul 10 '20 at 16:58

score 1 · Answer 1 · answered Jul 10 '20 at 12:08

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.

score 0 · Answer 2 · answered May 17 '22 at 16:06

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

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

2 Answers2