错误:“BY"处或附近的语法错误¶ 位置:321 [英] ERROR: syntax error at or near "BY"¶ Position: 321

问题描述

如何将以下 Oracle 查询转换为 Postgres?以下是错误

How to convert below Oracle query to Postgres? Below is the error

错误:BY"处或附近的语法错误¶ 位置:321

ERROR: syntax error at or near "BY"¶ Position: 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
  );

推荐答案

你没有解释这个查询是做什么的，而是复杂的connect by level 和 regexp_replace()是在 Oracle 中将逗号分隔的字符串拆分为元素的典型模式.

You did not explain what this query does, but the convoluted connect by level and regexp_replace() is a typically pattern to split a comma separated string into elements in Oracle.

在 Postgres 中可以更容易地做到这一点:

That can be done way easier in 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 运算符:

Note the duplicated parentheses around the sub-query are necessary. Another way is to use the IN operator:

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') 

这篇关于错误:“BY"处或附近的语法错误¶ 位置:321的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！