Sql ORA-00937: not a single-group group function

Applications Programming
1

I'm trying to return only one row with the highest value for PCT_MAX_USED. Any suggestions?

When I add this code, I get the ORA-00937 error.

trunc(max(decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100))) pct_max_used

This is the original and returns all rows.

select (select decode(extent_management,'LOCAL','*',' ')
         from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
         nvl(a.tablespace_name,'UNKOWN')) name,
      (decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100)) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
             max(bytes)/1024 largest,
             tablespace_name
      from  sys.dba_free_space
      group by tablespace_name, bytes) a,
    ( select sum(bytes)/1024 Kbytes_alloc,
             sum(maxbytes)/1024 Kbytes_max,
             tablespace_name
      from sys.dba_data_files
      group by tablespace_name
      union all
     select sum(bytes)/1024 Kbytes_alloc,
             sum(maxbytes)/1024 Kbytes_max,
             tablespace_name
      from sys.dba_temp_files
      group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name

*SYSAUX         	1.34277407778458
*SYSAUX         	1.34277407778458
*UNDOTBS1         	6.11114793355366
*BOB_DATA         	0.0183105468877898
*BOB_INDEX         	0.016242265712639
*BOB_DATA_2010         	7.450580602128E-5
*BOB_DATA_2005         	7.450580602128E-5
*BOB_DATA_2001         	7.450580602128E-5
*BOB_SUBSET         	       0.0480651855804481
2

Move dba_tablespaces to the from clause and group by the fields that are not aggregated. b.tablespace_name is never null, so nvl is superfluous:

select decode(c.extent_management,'LOCAL','*',' ') || b.tablespace_name name,
       max(trunc((decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100)))) pct_max_used
  from (select sum(bytes)/1024 Kbytes_free,
               max(bytes)/1024 largest,
               tablespace_name
          from sys.dba_free_space
         group by tablespace_name, bytes) a,
       (select sum(bytes)/1024 Kbytes_alloc,
               sum(maxbytes)/1024 Kbytes_max,
               tablespace_name
          from sys.dba_data_files
         group by tablespace_name
        union all
        select sum(bytes)/1024 Kbytes_alloc,
               sum(maxbytes)/1024 Kbytes_max,
               tablespace_name
          from sys.dba_temp_files
         group by tablespace_name )b,
       dba_tablespaces c 
 where a.tablespace_name (+) = b.tablespace_name
   and c.tablespace_name (+) = b.tablespace_name
 group by decode(c.extent_management,'LOCAL','*',' ') || b.tablespace_name
2 Likes
3

Thank you, that was a great answer.

I'm trying to get the truncated maximum single value from the above query.

I"m trying to find this:

BOB_DATA_2010         	7

I tried using order by and rownum = 1 but it didn't work.

Any ideas?

4

What does didn't work mean? You get wrong results or a syntax error? It works for me:

select * from
  (select decode(c.extent_management,'LOCAL','*',' ') || b.tablespace_name name,
         max(trunc((decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100)))) pct_max_used
    from (select sum(bytes)/1024 Kbytes_free,
                 max(bytes)/1024 largest,
                 tablespace_name
            from sys.dba_free_space
           group by tablespace_name, bytes) a,
         (select sum(bytes)/1024 Kbytes_alloc,
                 sum(maxbytes)/1024 Kbytes_max,
                 tablespace_name
            from sys.dba_data_files
           group by tablespace_name
          union all
          select sum(bytes)/1024 Kbytes_alloc,
                 sum(maxbytes)/1024 Kbytes_max,
                 tablespace_name
            from sys.dba_temp_files
           group by tablespace_name )b,
         dba_tablespaces c
   where a.tablespace_name (+) = b.tablespace_name
     and c.tablespace_name (+) = b.tablespace_name
   group by decode(c.extent_management,'LOCAL','*',' ') || b.tablespace_name
   order by pct_max_used desc)
 where rownum = 1

You have to use another layer of subquery because order by is applied last to the result set.

1 Like