Documentum DQL执行报错：SELECT DISTINCT TRIM语法解析错误求助

错误原因 #

Documentum DQL的语法解析器不支持在DISTINCT关键字后直接跟随带函数（如TRIM()）的列表达式，这是触发[DM_QUERY_E_SYNTAX]语法错误的核心原因——解析器无法识别SELECT DISTINCT TRIM(...)这种语法结构。

解决方案 #

提供两种可行的修复方案：

方法1：子查询预处理字段后去重 #

先在子查询中完成TRIM()对字段的格式化，再在外层查询用DISTINCT去重：

SELECT DISTINCT src, sep1, uname, sep2, ts, sep3, obj_name
FROM (
    SELECT TRIM(a.r_gen_source) AS src, '|' AS sep1, TRIM(a.user_name) AS uname, '|' AS sep2, TRIM(a.time_stamp) AS ts, '|' AS sep3, TRIM(d.object_name) AS obj_name
    FROM dm_audittrail a, dm_document d 
    WHERE a.time_stamp >= Date('01/01/2023', 'mm/dd/yyyy') 
      AND a.time_stamp <= Date('01/31/2023', 'mm/dd/yyyy') 
      AND a.audited_obj_id = d.r_object_id
)
ORDER BY ts ASC
GO

方法2：用GROUP BY替代DISTINCT #

通过GROUP BY对格式化后的字段分组，达到去重效果（需包含所有非聚合类字段）：

SELECT TRIM(a.r_gen_source), '|', TRIM(a.user_name), '|', TRIM(a.time_stamp), '|', TRIM(d.object_name)
FROM dm_audittrail a, dm_document d 
WHERE a.time_stamp >= Date('01/01/2023', 'mm/dd/yyyy') 
  AND a.time_stamp <= Date('01/31/2023', 'mm/dd/yyyy') 
  AND a.audited_obj_id = d.r_object_id
GROUP BY TRIM(a.r_gen_source), TRIM(a.user_name), TRIM(a.time_stamp), TRIM(d.object_name)
ORDER BY TRIM(a.time_stamp) ASC
GO

额外提示 #

原脚本中time_stamp字段未指定表别名，建议统一加上a.time_stamp避免表字段歧义（上述方案已修正）。

内容的提问来源于stack exchange，提问作者aintgel