PERCENTILE_CONT函数抛出错误

Question

我有这个疑问，我正在尝试引入非静态值到PERCENTILE_CONT：

SELECT perf2.REVIEW_PERIOD 
    , PERCENTILE_CONT(goalsASP.GOAL*.01) WITHIN GROUP (ORDER BY AVG_AMT ASC) ast75 
FROM repDB.TBL_PERFORMANCE perf2 JOIN 
     pz.CATEGORY C on perf2.DEPTCAT = C.id JOIN 
     repDB.TBL_GOALS_MATRIX goalsASP ON C.NAME = goalsASP.DIMENSION_Y 
             and perf2.REVIEW_PERIOD = goalsASP.SNAP_NAME 
             and goalsASP.DIMENSION_X = 'asp' 
GROUP BY perf2.REVIEW_PERIOD 

抛出的错误是：

ORA-30497: Argument should be a constant or a function of expressions in GROUP BY. 30497. 00000 - "Argument should be a constant or a function of expressions in GROUP BY."

这是一个查看，它工作正常时goalsASP.GOAL*.01.75.75和我有一个存储过程中提供一个列作为参数工作得很好，所以我有点在我在这里做错了一种损失。

Answer 1

明白了。这不是Percentile_Cont的问题，这是一个正确分组的问题。

要解决我孤立goalsASP.GOAL

SELECT perf2.REVIEW_PERIOD 
    , goalsASP.GOAL 
FROM repDB.TBL_PERFORMANCE perf2 JOIN 
     pz.CATEGORY C on perf2.DEPTCAT = C.id JOIN 
     repDB.TBL_GOALS_MATRIX goalsASP ON C.NAME = goalsASP.DIMENSION_Y 
             and perf2.REVIEW_PERIOD = goalsASP.SNAP_NAME 
             and goalsASP.DIMENSION_X = 'asp' 
GROUP BY perf2.REVIEW_PERIOD 

那么很明显，我需要还GROUP BY goalsASP.GOAL。

这样，那么，答案是：尴尬

SELECT perf2.REVIEW_PERIOD 
    , PERCENTILE_CONT(goalsASP.GOAL*.01) WITHIN GROUP (ORDER BY AVG_AMT ASC) ast75 
FROM repDB.TBL_PERFORMANCE perf2 JOIN 
     pz.CATEGORY C on perf2.DEPTCAT = C.id JOIN 
     repDB.TBL_GOALS_MATRIX goalsASP ON C.NAME = goalsASP.DIMENSION_Y 
             and perf2.REVIEW_PERIOD = goalsASP.SNAP_NAME 
             and goalsASP.DIMENSION_X = 'asp' 
GROUP BY perf2.REVIEW_PERIOD, 
     goalsASP.GOAL 

样，我没有之前看到的，但疲惫的眼睛错过这个东西。