Reputation: 23443
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;
Upvotes: 4
Views: 17600
Reputation: 52913
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...
Upvotes: 1
Reputation: 2303
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;
Upvotes: 3