Results 1 to 4 of 4

Thread: Stuck...

  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Unanswered: Stuck...

    Hi Guys;

    Hopefully one of you Oracle guru's can help me on this one...hopefully...

    Right basically what I want to do is process a result set with a load of regular expressions.

    The query to be processed with the expressions will return a text field and a number field.

    The regular expressions are stored in a table which has two columns that are needed; the regular expression (text) and a number (the modifier).

    For each row in the resultset, all the regular expressions must be appiled to it; if there is a match the modifier (from the regex. table) needs to be added to the number field in the query.

    Breif background: the result set returned by the query is an "alarm text" (i.e. "tank 1 is overflowing" or "tank 1 is empty"), the number returned is the default prority for that event.

    i.e. "tank 1 is overflowing",1

    now if the regex. matches this i.e. one of the entrys in the table is equal to "(overflowing)" and the modifer number is 1, the code needs to add 1 to the prority; making it 2...
    Any ideas?

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Even if you reply "your crazy to even think thats possible" please reply! Then i can think of a easier way!!!

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Even if *you* reply that's a crazy answer, it's the best I can come up with at this time ;-) I haven't even begun to check this works.....

    update incoming_data_table a
    set priority = (select priority_modifier from regex r where upper( a.alarm_text ) like '%'||upper(r.expression))||'%'

    However, when the resultant SQL gets like this, I would suggest the initial design needs looking at.

    HTH
    Bill

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    FYI everyone: I have done this code and it's not too bad;
    Code:
       (x varchar) return number  IS
    
    CURSOR c_test IS
    SELECT rule,adds
    FROM fyp.rules;
    
    foundMatch boolean:=false;
    v_rule fyp.rules.rule%TYPE;
    v_adds fyp.rules.adds%TYPE;
    v_count BINARY_INTEGER:=0;
    
    BEGIN
    OPEN c_test;
    LOOP
    FETCH c_test INTO v_rule,v_adds;
    EXIT WHEN c_test%NOTFOUND;
    
        foundMatch := owa_pattern.match(x, v_rule, 'i');
        if foundmatch=true then
          v_count:=v_count+v_adds;
        end if;
             
             
    END LOOP;
    CLOSE c_test;
    return v_count;
    END;
    this is set to run using a trigger each time a row is inserted. [and yeah i know it has no error handling...yet!]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •