使用"ELSE"中的选择. CASE语句的结果给我ORA-00937:不是单组分组函数 [英] Using select in the "ELSE" of a CASE statement gives me ORA-00937: not a single-group group function

问题描述

当我尝试在查询的ELSE部分中使用listagg进行选择语句时，出现 ORA-00937 错误.可以解决这个问题吗?

When I try to have a select statement using listagg in the ELSE portion of my query, I get ORA-00937 error. Any way around this?

select 
CASE 
    when count(*) = 0 then 
        'There are no users connected' 
    else 
        (select 
        listagg(osuser, ', ') within group (order by osuser) 
        from (select distinct osuser from v$session) 
        where osuser != 'SYSTEM' and osuser not like 'VMCONFTEST%')
end 
from v$session
where username is not null 
and osuser not like 'VMCONFTEST%';

即使我将select语句替换为更简单的内容，例如select osuser from v$session，我也会遇到相同的错误.

Even if I replace the select statement with something simpler, such as select osuser from v$session I get the same error.

推荐答案

采用略有不同的方法，但它似乎可以工作. 只需检查合计是否为空(coalesce返回序列中的第一个非空值)，以及它是否替代您的消息，而无需使用大小写并进行计数.这避免了我认为不需要的第二级分组.

Taking a slightly different approach but it appears to work. Instead of casing and doing a count, simply check if the aggregate is null (coalesce returns the first non-null value in a series) and if it is substitute your message. This avoids a 2nd level grouping which I don't believe is needed.

太糟糕的listagg也不支持聚合中的distinct；我们可以避免使用内联视图.

Too bad listagg doesn't support distinct within the aggregate as well; we could avoid the inline view.

SELECT coalesce(listagg(A.osuser, ', ') within group (order by A.osuser), 
                'There are no users connected') as userList
FROM (select distinct osuser from v$session) A
WHERE A.osuser!= 'SYSTEM' and A.osuser not like 'VMCONFTEST%'

这确实有开销，因为它试图生成用户列表，您的case语句可能试图将这些用户列表短路.但是，如果在V $ session中没有记录，则选择不同的记录应该很快.

This does have the overhead in that it attempts to generate a list of users which your case statement may be attempting to short circuit. However if there are no records in V$session the select distinct should be quick.

尽管说实话，我不确定为什么我们需要这样做.列表中的Null通常是足够的响应，指示没有用户.并且用户界面将处理null，表示没有用户.

Though to be honest I'm not sure why we need to do this. Null in the list is generally an adequate response indicating no users. and the UI would handle null meaning no users.

如果我们向内联视图添加where子句，甚至可能会更快.

May even be faster if we more the where clause to the inline view..

SELECT coalesce(listagg(A.osuser, ', ') within group (order by A.osuser), 
                'There are no users connected') as userList
FROM (SELECT distinct osuser 
      FROM v$session
      WHERE A.osuser!= 'SYSTEM' 
        and A.osuser not like 'VMCONFTEST%') A

这篇关于使用"ELSE"中的选择. CASE语句的结果给我ORA-00937:不是单组分组函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！