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;

Are you tying to put user name meeting reg ex in one group rest on there user name? — ejb_guy, May 26 '12 at 14:43
@ejb_guy please see here: http://stackoverflow.com/questions/10763043/sql-how-to-group-by-with-a-special-condition — Oh Chin Boon, May 26 '12 at 14:44
Basically, it is trying to check if the username is numeric, e.g. 34535, overrideit as GRP_OTHERS and ad this count to GRP_OTHERS. — Oh Chin Boon, May 26 '12 at 14:46

score 3 · Accepted Answer · answered May 26 '12 at 14:25

3

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;

answered May 26 '12 at 14:25

jswolf19

2,303
15
16

Thanks. It still gives me the same error, pin-points to row/col somewhere here "... +$') THEN ..." – Oh Chin Boon May 26 '12 at 14:34
A couple more thoughts, then. I'm not sure if Oracle regexp supports `\d`. You may want to use `[0-9]` or `[:digit:]` instead. What version of Oracle are you using? Is it possible that it doesn't recognize `REGEXP_LIKE` for some reason? Try something like `select 1 from dual where regexp_like('123', '^[0-9]$')` and see if you get an error... – jswolf19 May 26 '12 at 14:47
i'm using 9i, i will check on your recommendation now. – Oh Chin Boon May 26 '12 at 14:50
1

Ah, it appears `REGEXP_LIKE` was introduced in 10G... [This](http://phil-sqltips.blogspot.jp/2009/06/regexpreplace-regexplike-for-oracle-9i.html) might help you out... Or you can do some [googling](https://www.google.com/search?hl=en&q=REGEXP_LIKE+oracle+9i) to find other workarounds ^_^ – jswolf19 May 26 '12 at 14:56
Thanks. I am accepting this as an answer, never expected this to be due to 9i. – Oh Chin Boon May 26 '12 at 14:59

score 1 · Answer 2 · edited May 23 '17 at 11:51

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...

thank you, it seems that i am using Oracle 9i, and it does not fully support regexp_like. — Oh Chin Boon, May 26 '12 at 15:05
http://stackoverflow.com/questions/10763043/sql-how-to-group-by-with-a-special-condition would you have an alternative to this, knowing that regexp_like doesn't work in 9i — Oh Chin Boon, May 26 '12 at 15:08

ORA-00920: invalid relational operator

2 Answers2