错误：语法错误在"BY"或接近 ¶ 位置： 321

Question

如何将下面的Oracle查询转换为Postgres？以下是错误

错误：在"处或附近出现语法错误；BY"；¶位置：321

查询

SELECT listagg(app_rule_cd,',') within GROUP (
ORDER BY abc_cd) AS ERR_LST,
'1'                 AS JOIN1
FROM ABC_RULE
WHERE abc_cd IN
( WITH CTE AS
(SELECT VAL FROM config_server WHERE NAME = 'XXXXXXXXXX'
)
SELECT TRIM(REGEXP_SUBSTR( VAL, '[^,]+', 1, LEVEL))
FROM CTE
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(VAL, '[^,]+')) + 1  // BY is position 321
);

Answer 1

您没有解释这个查询的作用，但复杂的connect by level和regexp_replace()是在Oracle中将逗号分隔的字符串拆分为元素的典型模式。

这可以在Postgres:中更容易地完成

SELECT string_agg(app_rule_cd,',' ORDER BY abc_cd) AS ERR_LST,
'1'                                         AS JOIN1
FROM ABC_Rule
WHERE abc_cd = ANY ( (SELECT string_to_array(val, ',')
FROM config_server WHERE NAME = 'XXXXXXXXXX') )

请注意，子查询周围的重复括号是必要的。另一种方法是使用IN运算符：

SELECT string_agg(app_rule_cd,',' ORDER BY abc_cd) AS ERR_LST,
'1'                 AS JOIN1
FROM ABC_Rule
WHERE abc_cd IN (SELECT unnest(string_to_array(val, ','))
FROM config_server 
WHERE NAME = 'XXXXXXXXXX')