3

I have a table with log messages that many times contain repeated information with slightly different values. For example:

Unable to make thread 19043 a realtime process
Unable to make thread 20763 a realtime process
Unable to make thread 22179 a realtime process
FEED_XYZ Secondary Instrument Lines not configured
FEED_ZZZ Secondary Instrument Lines not configured
(...)

I'd like to have just one line from each group above:

Unable to make thread 19043 a realtime process
FEED_XYZ Secondary Instrument Lines not configured

Is there a way to use a select query to retrieve distinct rows with utl_match.jaro_winkler_similarity?

Something like:

select log_message
from logs_table
where "utl_match.jaro_winkler_similarity between rows < 80"

I know that I can make a pipelined function or some other PL/SQL routine, but I just would like to double-check if there's some easier way of doing this.

2

I'm not overly familiar with the UTL_MATCH.JARO_WINKLER_SIMILARITY function, but using it in a UNION query, in conjunction with the ROW_NUMBER analytic function, will give you the result you desire:

WITH
    temp AS
    (
    SELECT log_message, ROW_NUMBER() OVER (ORDER BY log_message) rn
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'Unable to make thread 19043 a realtime process') > 80
    UNION
    SELECT log_message, ROW_NUMBER() OVER (ORDER BY log_message) rn
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'FEED_XYZ Secondary Instrument Lines not configured') > 80
    )
SELECT log_message
FROM temp
WHERE rn = 1
;

Result (screenshot from test DB at apex.oracle.com):

enter image description here

If you just need any value from each group (as opposed to the "first" value in each sorted group), you could use ROWNUM criteria instead of the ROW_NUMBER functions, which would return a similar result:

WITH
    temp AS
    (
    SELECT log_message
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'Unable to make thread 19043 a realtime process') > 80
      AND ROWNUM = 1
    UNION
    SELECT log_message
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'FEED_XYZ Secondary Instrument Lines not configured') > 80
      AND ROWNUM = 1
    )
SELECT log_message
FROM temp
;


However, unless there is a specific reason why you need to use UTL_MATCH.JARO_WINKLER_SIMILARITY, there's a chance you may be falling victim to the XY Problem. You could achieve identical results by replacing the two individual WHERE-clause lines (in either query above) with these:

WHERE log_message LIKE 'Unable to make thread %'
WHERE log_message LIKE 'FEED\_%' ESCAPE '\'

Or these (e.g. if there is a need for more fine-grained filtering):

WHERE REGEXP_LIKE(log_message, 'Unable\sto\smake\sthread\s\d+\sa\srealtime\sprocess')
WHERE REGEXP_LIKE(log_message, 'FEED_[A-Z]{3}\sSecondary\sInstrument\sLines\snot\sconfigured')
0

Thank you for your answer, but the point here is: I want to have a generic SQL that will return just the unique "log_message" from "logs_table". The example was just to illustrate. I ended up creating a pipelined function (sorry for the extra tables, but this was a final solution to my environment):

function get_alert_messages return rl_alert_info_table pipelined as
  /*
    This function returns the distinct error messages using the algorithm Jaro Winkler to get rid
    of slightly different strings.
    More info: http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/u_match.htm
    Example of use:
    select * from table(rl.get_alert_messages())
  */
  cursor logs_cur is select distinct r.customer_id, r.report_id, r.server_id, r.name as report_name, li.message
                       from reports r, logs l, logs_info li
                      where r.dateandtime > to_timestamp(to_char(current_timestamp,'DD-MON-YYYY')||' 01:00:00') and -- beginning of the day
                              r.report_id = l.report_id and 
                              li.log_id = l.log_id and
                              li.type_id = 4 and
                              li.timedetail > to_timestamp(to_char(current_timestamp,'DD-MON-YYYY')||' 01:00:00') 
                      order by r.customer_id, r.report_id, li.message; -- the order by is important here.
  l_message logs_cur%rowtype;
  l_last_msg logs_info.message%type;
begin
  begin
  open logs_cur;
  l_last_msg := ''; -- this variable stores the message from the previous row.
  loop 
    fetch logs_cur into l_message;
    exit when logs_cur%notfound;
      if utl_match.jaro_winkler_similarity(l_last_msg,l_message.message) < 85 then -- if the level of similarity is less than 85%
        pipe row(rl_alert_info(l_message.customer_id,
                              l_message.server_id,
                              l_message.report_name,
                              l_message.message));
     end if;
      l_last_msg := l_message.message;
  end loop;
  close logs_cur;
  exception
    when others then 
      close logs_cur;
      raise;
  end;
  return;
end get_alert_messages;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.