U0100078: GaussDB does not support DISTINCT in LISTAGG

Description

Database Type and Version

• Source database type and version: Oracle versions supported by UGO
• Target database type and version: GaussDB of versions earlier than V2.0-9.0

Syntax Example

This error is reported because GaussDB of versions earlier than V2.0-9.0 does not support DISTINCT in an aggregation column expression of the LISTAGG function, for example:

SELECT LISTAGG(DISTINCT col,';') WITHIN GROUP(ORDER BY col) FROM listagg_t1;

Suggestion

You are advised to refactor application code or upgrade GaussDB. The following is an example of refactoring application code:

SELECT LISTAGG(col, ';') WITHIN GROUP(ORDER BY col)
FROM (
  SELECT col, ROW_NUMBER() OVER (PARTITION BY col ORDER BY col) AS rn  FROM listagg_t1
) t WHERE rn = 1;