SQL ORA-30483: 此处不允许使用窗口函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2119648/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-30483: window functions are not allowed here
提问by Andreas
Does anyone know how to get around this error ? this sql works fine, but for the 'cumulinvoiced' partitioning function. i'm trying to add a cumulative total, but it doesn't seem to work on inline views.
有谁知道如何解决这个错误？这个 sql 工作正常，但对于 'cumulinvoiced' 分区函数。我正在尝试添加累积总数，但它似乎不适用于内联视图。
SELECT a.mois, a.supid, a.status, COUNT (a.status),
SUM (COUNT (a.status) OVER (ORDER BY a.status, a.supid, a.dossier, a.mois)) cumulinvoiced
from (SELECT TO_CHAR (impdate, 'YYYYMM') mois,
benefit#sup_inv_id supid,
SUBSTR (si_benstatus, 1, 1) status,
files#fil_secid dossier
FROM sbbenefit
WHERE benefit#sup_inv_id = 30515
AND SUBSTR(si_benstatus, 1, 1) = '2'
GROUP BY TO_CHAR (impdate, 'YYYYMM'), benefit#sup_inv_id ,SUBSTR (si_benstatus, 1, 1), files#fil_secid) a
GROUP BY a.mois, a.supid, a.status
order BY a.supid, a.mois , a.status
回答by Quassnoi
The aggregate COUNTshould be an argument to the analytic SUM, not vice versa:
聚合COUNT应该是 analytic 的参数SUM，反之亦然：
SELECT a.mois, a.supid, a.status, COUNT (a.status),
SUM (COUNT (a.status)) OVER (ORDER BY a.status, a.supid, a.dossier, a.mois) cumulinvoiced
from (SELECT TO_CHAR (impdate, 'YYYYMM') mois,
benefit#sup_inv_id supid,
SUBSTR (si_benstatus, 1, 1) status,
files#fil_secid dossier
FROM sbbenefit
WHERE benefit#sup_inv_id = 30515
AND SUBSTR(si_benstatus, 1, 1) = '2'
GROUP BY TO_CHAR (impdate, 'YYYYMM'), benefit#sup_inv_id ,SUBSTR (si_benstatus, 1, 1), files#fil_secid) a
GROUP BY a.mois, a.supid, a.status
order BY a.supid, a.mois , a.status
回答by Dave Costa
Analytics and grouping don't tend to work together. If you perform the grouping first then do the analytic query against that result, I think it should work:
分析和分组不倾向于一起工作。如果您先执行分组，然后对该结果进行分析查询，我认为它应该有效：
SELECT mois, supid, status, status_count,
SUM (status_count OVER (ORDER BY a.status, a.supid, a.dossier, a.mois)) cumulinvoiced
from
(SELECT a.mois, a.supid, a.status, COUNT (a.status) status_count
from
(
SELECT
TO_CHAR (impdate, 'YYYYMM') mois,
benefit#sup_inv_id supid,
SUBSTR (si_benstatus, 1, 1) status,
files#fil_secid dossier
FROM sbbenefit
WHERE benefit#sup_inv_id = 30515
AND SUBSTR (si_benstatus, 1, 1) = '2'
GROUP BY
TO_CHAR (impdate, 'YYYYMM'),
benefit#sup_inv_id ,
SUBSTR (si_benstatus, 1, 1),
files#fil_secid
) a
GROUP BY
a.mois, a.supid, a.status
)
order BY
supid, mois , status
