ORA-00920: invalid relational operator

Question

This SQL query seems to be hitting ORA 00920.

select username, count(*)
from host
where created_dt 
between to_date('2012-may-23 00:00:00', 'yyyy-mon-dd hh24:mi:ss') 
and to_date('2012-may-23 23:59:59', 'yyyy-mon-dd hh24:mi:ss')
GROUP BY CASE
             WHEN REGEXP_LIKE(username, '^\d+$') THEN 'GRP_OTHERS'
                                                 ELSE username
         END;

Solution 1

I don't have an Oracle DB to play with, but I imagine it could be because you select username but don't group by it. You should be able to get around that by using a subquery:

select username, count(*)
from (select CASE
             WHEN REGEXP_LIKE(username, '^\d+$') THEN 'GRP_OTHERS'
                                                 ELSE username
         END as username
      from host
      where created_dt 
      between to_date('2012-may-23 00:00:00', 'yyyy-mon-dd hh24:mi:ss') 
          and to_date('2012-may-23 23:59:59', 'yyyy-mon-dd hh24:mi:ss')
     )
GROUP BY username;

Solution 2

I don't quite trust what you're posted...

Your query should throw ORA-00979: Not a GROUP BY expression. This is because the columns not included in an analytic function, namely username are not reflected in your group by.

ORA-00920 implies that you are missing one of the following: =, <>, not in, in, !=, is not null, is null, not like, like etc. Are you sure you've posted the correct query?

In 11.2, creating something that approximates your table, like so:

create table host
(  username varchar2(100)
 , created_dt date );

 insert into host
 select level, sysdate - level
   from dual
connect by level <= 10
        ;

 insert into host
 select chr(ascii(level) + 32), sysdate - level
   from dual
connect by level <= 10
        ;
 commit ;

and then running the query posted results in ORA-00979. Changing this to the following work's fine.:

select case when regexp_like(username, '^\d+$') then 'GRP_OTHERS'
            else username end as username
     , count(*)
  from host
 where created_dt between
       to_date('2012-may-23 00:00:00', 'yyyy-mon-dd hh24:mi:ss') and
       to_date('2012-may-23 23:59:59', 'yyyy-mon-dd hh24:mi:ss')
 group by case when regexp_like(username, '^\d+$') then 'GRP_OTHERS'
               else username end
       ;

This can alternatively be re-written as:

select distinct username
     , count(*) over ( partition by case when regexp_like(username, '^\d+$') 
                                               then 'GRP_OTHERS'
                                          else username end )
  from host
 where created_dt between
       to_date('2012-may-23 00:00:00', 'yyyy-mon-dd hh24:mi:ss') and
       to_date('2012-may-23 23:59:59', 'yyyy-mon-dd hh24:mi:ss')

I think this second query is more like what you want. It returns the actual user-name, but groups all those that are just digits together. Just replace the username column returned by the case if you want to see GRP_OTHERS instead.

It's slightly better not to use the mon format model in Oracle as it's not, necessarily, consistent across languages. Use mm instead.

---

As you're using 9i you can use translate instead. Replace the regexes with:

trim(translate(username,'0123456789',' ')) is null

This replaces numbers with nothing and then checks to see if there's anything left...