CODEWITHSUNDEEP
oracle-databasecase
pwj21090
pwj21090

Reputation: 25

Using select in the "ELSE" of a CASE statement gives me ORA-00937: not a single-group group function

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%';

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

Upvotes: 2

Views: 567

Answers (4)

xQbert
xQbert

Reputation: 35323

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.

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%'

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.

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.

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

Upvotes: 4

CodeNinja
CodeNinja

Reputation: 23

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

The problem was the count that cannot stand there

In plus when I need to perform complex subqueries I love to use with clause Probably this query can be improved centralizing the conditions (that are duplicated)

Upvotes: 0

Chrisrs2292
Chrisrs2292

Reputation: 1094

Your statement is missing a group by clause, the below for example works:

select distinct(
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%'
GROUP BY osuser;

Upvotes: 1

SkyWalker
SkyWalker

Reputation: 494

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

Upvotes: 0

Related Questions