Oracle SQL - ListAgg - ORA-00923:在预期位置找不到 FROM 关键字

Question

>我有一个表格结构如下

PERSON_ID | SUFFIX | Details
-----------------------------
12345     | Mr     | ABC
12345     | Mr     | DEF
12345     | Mr     | GHI

这是我的 SQL

SELECT PERSON_ID
    ,SUFFIX
    ,LISTAGG(Details, ' ') WITHIN
GROUP (
        ORDER BY PERSON_ID
        ) AS Deets
FROM TABLE_NAME
GROUP BY PERSON_ID
    ,SUFFIX

所以我想将多行放入一列中。但是，我收到以下错误：

ORA-00923：在预期位置找不到 FROM 关键字

知道为什么会这样吗？我的TABLE_NAME是使用 WITH AS 语句构建的。我可以很好地从TABLE_NAME中提取数据，将数据和所有"正常"的东西分组

完整代码

WITH
person_id
AS (
    SELECT DISTINCT person_id
    FROM PER_ALL_PEOPLE_F
),
suffix AS (
SELECT person_id, suffix 
FROM PER_PERSON_NAMES_F 
WHERE
    NAME_TYPE = 'GLOBAL' 
     AND TRUNC(SYSDATE) BETWEEN TRUNC(EFFECTIVE_START_date) AND TRUNC(EFFECTIVE_END_DATE) 
GROUP BY person_id, suffix
)
,
person_id_data
AS (
    SELECT DISTINCT person_id
    FROM PER_ALL_ASSIGNMENTS_M
    WHERE person_id IN (SELECT person_id FROM person_id)
    AND ASSIGNMENT_STATUS_TYPE = 'ACTIVE' 
),
-- GETTING NUMBERS
Contact_Phone_Numbers As (SELECT 
    PERSON_ID, 
    OBJECT_VERSION_NUMBER, 
    PHONE_ID, 
    DATE_FROM, 
    DATE_TO, 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PHONE_TYPE,'O','Other'),'HM','Home Mobile'),'HF','Home Fax'),'H3','Third Home'),'H2','Second Home'),'H1','Home'),'WM','Work Mobile'),'WF','Work Fax'),'W3','Third Work'),'W2','Second Work'),'W1','Work') AS PHONE_TYPE, 
    PHONE_NUMBER, 
    SEARCH_PHONE_NUMBER, 
    COUNTRY_CODE_NUMBER 
FROM 
    PER_PHONES 
WHERE 
    PERSON_ID IN (SELECT person_id FROM person_id_data))
,
NEARLY_THERE AS (
    SELECT A.PERSON_ID, A.PHONE_TYPE, A.PHONE_NUMBER, B.SUFFIX
    FROM Contact_Phone_Numbers A
    LEFT JOIN suffix B
    ON A.PERSON_ID = B.PERSON_ID
    ORDER BY A.PERSON_ID
), 
SO_CLOSE AS (
SELECT PERSON_ID, CONCAT(PHONE_TYPE, CONCAT (' - ' , PHONE_NUMBER)) AS THE_DETAILS, SUFFIX
FROM NEARLY_THERE
ORDER BY PERSON_ID
),
FINAL AS (
    SELECT * FROM SO_CLOSE
)
SELECT PERSON_ID, SUFFIX, LISTAGG(THE_DETAILS, ' ') WITHIN GROUP (ORDER BY PERSON_ID) AS Deets
FROM FINAL 
GROUP BY PERSON_ID, SUFFIX

Answer 1

(这更像是一个注释，但对于实际注释来说太多了/格式(

如果运行非 CTE 版本会发生什么情况？

SELECT 
  paam.person_id, 
  ppnf.suffix,
  LISTAGG(
    CONCAT(
      DECODE(pp.PHONE_TYPE,'O','Other','HM','Home Mobile','HF','Home Fax','H3','Third Home','H2','Second Home','H1','Home','WM','Work Mobile','WF','Work Fax','W3','Third Work','W2','Second Work','W1','Work'),
      ' - ',
      pp.PHONE_NUMBER
    ),
    ' '
  ) WITHIN GROUP (ORDER BY PERSON_ID) as Deets
FROM 
  PER_ALL_ASSIGNMENTS_M paam
  INNER JOIN 
  PER_ALL_PEOPLE_F papf
  ON paam.person_id = papf.person_id
  INNER JOIN 
  PER_PHONES pp
  ON
    paam.person_id = pp.person_id
  LEFT JOIN 
  PER_PERSON_NAMES_F ppnf
  ON 
    paam.PERSON_ID = ppnf.PERSON_ID AND
    ppnf.NAME_TYPE = 'GLOBAL' AND
    TRUNC(SYSDATE) BETWEEN TRUNC(ppnf.EFFECTIVE_START_date) AND TRUNC(ppnf.EFFECTIVE_END_DATE) 
WHERE
  paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' 
GROUP BY PERSON_ID, SUFFIX

注意;一些小的语法错误和结果差异可能会被看作是此查询省略了 CTE 表单具有的一些 DISTINCT 操作。我建议您在没有listagg/group的情况下修复它，这样就不会有重复的行(对where子句/连接条件进行排序，以便消除笛卡尔积，而不是稍后将它们区分开来(

即修复此问题：

SELECT 
  paam.person_id, 
  ppnf.suffix,
    CONCAT(
      DECODE(pp.PHONE_TYPE,'O','Other','HM','Home Mobile','HF','Home Fax','H3','Third Home','H2','Second Home','H1','Home','WM','Work Mobile','WF','Work Fax','W3','Third Work','W2','Second Work','W1','Work'),
      ' - ',
      pp.PHONE_NUMBER
    )
FROM 
  PER_ALL_ASSIGNMENTS_M paam
  INNER JOIN 
  PER_ALL_PEOPLE_F papf
  ON paam.person_id = papf.person_id
  INNER JOIN 
  PER_PHONES pp
  ON
    paam.person_id = pp.person_id
  LEFT JOIN 
  PER_PERSON_NAMES_F ppnf
  ON 
    paam.PERSON_ID = ppnf.PERSON_ID AND
    ppnf.NAME_TYPE = 'GLOBAL' AND
    TRUNC(SYSDATE) BETWEEN TRUNC(ppnf.EFFECTIVE_START_date) AND TRUNC(ppnf.EFFECTIVE_END_DATE) 
WHERE
  paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' 

然后重新引入 LISTAGG