Hi,
BANNER
-------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production
I am given a log table tlog that has a few mio records, and need to group the messages based on their pattern. My problem is that the message are not defined anywhere, so I have created my own definition in table regx. Currently, this table has 65 records and I expect it to grow to perhaps 100-200.
These are the tables:
drop table tlog purge;
drop table regx purge;
create table tlog (id number(9) primary key, msg varchar2(55));
create table regx (id number(9) primary key, pattern varchar2(55) not null unique);
insert into tlog values(1, 'Error where item = 1234');
insert into tlog values(2, 'Error where item = 2345');
insert into tlog values(3, 'Discard due to code = 123 on customer');
insert into tlog values(4, 'Discard due to code = 234 on customer');
insert into tlog values(5, 'Discard due to code = 345 on customer');
insert into tlog values(6, 'Discard due to code = 123 on vendor = 456');
insert into tlog values(7, 'Discard due to code = 234 on vendor = 567');
insert into tlog values(8, 'Discard due to code = 345 on vendor = 678');
insert into tlog values(9, 'Discard due to code = 456 on vendor = 789');
insert into regx values (100, 'Error where item =%');
insert into regx values (200, 'Discard due to code =%on customer');
insert into regx values (300, 'Discard due to code =%on vendor =%');
commit
/
select * from tlog;
select * from regx;
/
Now, I can do something like this:
select regx.id, count(*)
from regx, tlog
where tlog.msg like regx.pattern
group by regx.id
order by regx.id;
ID COUNT(*)
---------- ----------
100 2
200 3
300 4
3 rows selected.
So far so good. My problem now is that I have messages that does not fall into
any of the categories. Consider
insert into tlog values(10, 'Other kind of message');
insert into tlog values(11, 'Another kind of message');
insert into tlog values(12, 'Yet, another');
insert into regx values (400, '%');
commit
/
select regx.id, count(*)
from regx, tlog
where tlog.msg like regx.pattern
group by regx.id
order by regx.id;
ID COUNT(*)
---------- ----------
100 2
200 3
300 4
400 12 wrong!
4 rows selected.
The correct answer for group *400* would be 3.
Any ideas on how to handle these other messages?
Any changes to the regx table, including contents and definition is possible. Changes to tlog table is not.
Regards
Peter
Message was edited by:
Peter Gjelstrup (Added banner)
Message was edited by:
Peter Gjelstrup [SOLVED]