ORA-06553:请-306多天最大计数

Question

我有一个查询，它可以在特定的一天获取事件计数。

select eventid_nbr, trunc(received_date, 'DD'), sentindicator, count (eventid_nbr) as count
from eventlog
where received_date >= to_date('2017-07-01','YYYY-MM-DD') and sentindicator = 'Y'
group by eventid_nbr, trunc(received_date, 'DD'), sentindicator
order by trunc(received_date, 'DD');

但是，我需要x天的最大计数，并修改我的查询如下

select y.eventid_nbr, trunc(y.received_date, 'DD'), y.sentindicator, max(y.count)
from (select count(eventid_nbr) as count from eventlog) y
where y.received_date between to_date('2017-07-01','YYYY-MM-DD') and to_date('2017-07-03','YYYY-MM-DD') and y.sentindicator = 'Y'
group by y.eventid_nbr , trunc(y.received_date, 'DD'), y.sentindicator
order by trunc(y.received_date, 'DD');

执行结果

ORA-06553:请-306:调用“OGC_Y”的参数数目或类型错误

我为什么要收到这个问题？堆栈有类似的问题报告here，但我不使用双引号。

Answer 1

您需要找到最大值(Count())，然后返回所有匹配的组/天：

select eventid_nbr, trunc(received_date, 'DD'), sentindicator, count (eventid_nbr) as count
from eventlog
where y.received_date between to_date('2017-07-01','YYYY-MM-DD') and to_date('2017-07-03','YYYY-MM-DD') and sentindicator = 'Y'
group by eventid_nbr, trunc(received_date, 'DD'), sentindicator
having count(eventid_nbr) = (select max(count)
                             from (select count(eventid_nbr) as count
                                   from eventlog
                                   where y.received_date between to_date('2017-07-01','YYYY-MM-DD') and to_date('2017-07-03','YYYY-MM-DD') and sentindicator = 'Y'
                                   group by eventid_nbr, trunc(received_date, 'DD'), sentindicator) )
order by trunc(received_date, 'DD');