对于select语句中具有用户定义函数的查询，ORA-00979不是Group函数错误

Question

我有一个查询，其中在select和groupby语句中添加了一个用户定义的函数。

不带WITH子句的内部select查询运行良好，不会出现任何错误。但在添加WITH子句后，它给出了以下错误-

ORA-00979：不是GROUP BY表达式00979.00000-"；而不是GROUP BY表达式"；*原因：
*措施：第3行第29列错误

我需要WITH子句只返回基于输入范围的整个结果集的子集。

查询如下：

WITH INFO AS (
SELECT 
GET_EVAULATED_VALUE(T.C_IMP, T.IMP) AS IMPORTANCE,
count(*) AS NO_OF_PC_AFFECTED 
FROM TABLE_NAME T 
WHERE T.ACNT_REL_ID = 16 
GROUP BY 
(GET_EVAULATED_VALUE(T.C_IMP, T.IMP))
ORDER BY IMPORTANCE desc 
)
SELECT * FROM 
( 
SELECT ROWNUM AS RN,
(SELECT COUNT(*) FROM INFO) COUNTS,
IMPORTANCE
FROM INFO
)
WHERE RN > 0 AND RN <= 10;

Answer 1

我不知道如何在用户定义的函数上使用CTE和groupby。但我意识到，我可以重写查询以删除子查询和CTE，并使其更简单，如下所示(它有效(：

select * from ( 
select a.*, ROWNUM rnum from
(SELECT
count(*) over() as COUNTS,
GET_EVAULATED_VALUE(T.C_IMP, T.IMP) AS IMPORTANCE,
count(*) AS NO_OF_PC_AFFECTED
FROM TABLE_NAME T 
WHERE T.ACNT_RELATION_ID = 16
GROUP BY
(GET_EVAULATED_VALUE(T.C_IMP, T.IMP))
ORDER BY importance desc) a
where ROWNUM <= 10 )
where rnum >= 0; 

Answer 2

同样的问题，我创建了一个表"TABLE_CTE"；而不是使用CTE，它起了作用。

CREATE TABLE TABLE_CTE
AS
SELECT
USER_DEFINED_FUNCTION(date_1),
COUNT(*)
FROM 
TABLE_NAME
GROUP BY 
USER_DEFINED_FUNCTION(date_1)
;

SELECT * FROM TABLE_CTE