0

I have a little question. So I'm using the Levenshtein-score to search for a comparison of more than 85% between street names in two different tables. But when I use my Levenshtein-score calculation in my WHERE-statement, I get as output for example this:

  • street names in one table: BEAU SITE 1ÈRE AVENUE & BEAU SITE 2ÈME AVENUE
  • street names in the other table: BEAU SITE-1ÈRE AVENUE & BEAU SITE-2ÈRE AVENUE
  • output: linking between all, first one with first and second on, and second one with first and second.

So I have to use the largest score for all the score-calculations and is this like this:

    DECLARE    
    L_SCORE NUMBER;    
    L_NEW_SCORE NUMBER;    
    L_BEST_MAP varchar2(255);
    CURSOR C_TO_FIND IS    
    SELECT TT_NAME, L_MUNI, R_MUNI    
    FROM Y_TT_NOT_LINKED_STREETS    ;    
    CURSOR C_TOMTOM_STREET (L_MUNI VARCHAR2) IS    
    SELECT STREET_NAME    
    FROM STREET_NAME SN
                JOIN STREET STR ON STR.STREET_ID = SN.STR_STREET_ID
                JOIN ADMINISTRATIVE_AREA_NAME AAN ON AAN.AAR_ADMIN_AREA_ID = STR.AAR_ADMIN_AREA_ID    
    WHERE AAN.ADMIN_AREA_NAME = L_MUNI
                 AND STR.STREET_ID NOT IN (SELECT ROMA_STREET_ID FROM Y_DS_STREETS_LINK);
BEGIN   
FOR S IN C_TO_FIND LOOP
              L_SCORE := 0;
              L_NEW_SCORE := 0;
    FOR R IN C_TOMTOM_STREET(S.L_MUNI) LOOP
                  L_SCORE := PCK$ADDRESSMATCH.GET_LEVENSHTEIN_SCORE(S.TT_NAME,R.STREET_NAME);
                  IF L_SCORE > L_NEW_SCORE  THEN
                     L_NEW_SCORE := L_SCORE ;
                     L_BEST_MAP := R.STREET_NAME ||CHR(9)||TO_CHAR(L_NEW_SCORE);
                  END IF;
    END LOOP;
    IF L_NEW_SCORE > 85 THEN
                 DBMS_OUTPUT.PUT_LINE(S.L_MUNI||CHR(9)||S.TT_NAME||chr(9)||L_BEST_MAP);
    END IF;
    L_NEW_SCORE := 0;   
END LOOP; 
END;

Now is the question, how can I use the output in a WHERE-statement, so that I can link with only the largest Levenshtein-score and the problem above will not occur? So that: SELECT ... FROM ... WHERE (largest score from previous block code)

(or on another way, after a whole week doing SQL I can't see a solution for this)

Thx! =)

NYannickske
  • 131
  • 1
  • 1
  • 4
  • Not sure I understood your output in where statement... Maybe you meant how to use output of some other PL/SQL code in your where clause? Then the answer is use Function. Functions can be used in Select statements, like Select your_function() From dual OR Select smth Where your_column = (SELECT your_function() FROM DUAL); – Art Feb 01 '13 at 16:26
  • that is a very good idea, I will give it a go tomorrow. Thanks for your help! – NYannickske Feb 03 '13 at 12:55

0 Answers0