Performing Fuzzy-Search on 2 tables

[Former Member]

Hello community,

I have got a problem with performing Fuzzy-Search on two tabels.

There are the tables and the columns, I would like to perform the fuzzy-search on:

I would like to search in both tables in the following columns for "Müller" with the Fuzzy-Search-Option "bestMatchingTokenWeight" with a score of 0.7.

1) Name of Table 1: LIEFERANTENSTAMMTABELLE

   Columns of Table 1:

• Name1
• Name2
• Name3
• Name4
• Ort
• Ortsteil
• Strasse

1) Name of Table 2: KUNDENSTAMMTABELLE

   Columns of Table 2:

• Name1
• Name2
• Strasse

I have created the following SQL-SELECT-Statement but it doesn't work:

Please can you tell me, what's wrong with my SQL-Statement?

I think there is a mistake in the JOIN.

Thank you very much for helping.

Best Rergards,

Michael

[lbreddemann · Accepted solution]

Ok, just guessing here, but this is what pops to my mind:

You JOIN the two master data tables.

Why?

And if you do join, you should provide join conditions.

What you create there is essentially a n x m set of your master data.

That doesn't make sense to me.

Better and probably more correct would be to UNION the two tables.

You could put that into a view and you would have just one view to query.

(another approach would of course be to model the address information in one table and maintain the relationship VENDOR/CUSTOMER/PARTNER/... in a separate table - but hey, it's your data model)

The next thing is: score() is a measure for the fitness of a match to a search condition.

I've no idea how that will work, if you combine these search conditions and multiple conditions apply.

Finally:

You wrote "it doesn't work" ... any error messages?

- Lars

[Former Member · Accepted solution]

Hello Lars,

thank you very much for your fast answer.

I do not know, if I HAVE to join these two tabels.

I only would like to perform the search on the two tables, maybe a join isn't neccessary to do that.

I only whant to put a query against these 2 tables with the relevant / accordant colums.

Can you tell me, how I can do this?

This was the error message, when I tried to execute the SQL-Statement:

Could not execute 'SELECT SCORE() AS score, * FROM LIEFERANTENSTAMMTABELLE JOIN KUNDENSTAMMTABELLE ON ...'

SAP DBTech JDBC: [257] (at 116): sql syntax error: incorrect syntax near "WHERE": line 5 col 1 (at pos 116)

Thank you and best regards,

Michael

[lbreddemann · Accepted solution]

Hi Michael,

as written before, I don't think that joining the tables is what you want to do here.

If I get your requirement then you want to look for data that is either in the one or the other table.

You're not looking for data that is in the one table but also occurs in the other table.

To do just what you want, you need to UNION both tables.

Do something like:

SELECT 'VENDOR' as "CONTACT_TYPE", name1, name2, name3, score() as "SCORE"
FROM "VENDORTABLE"
WHERE CONTAIN ("NAME1", 'Müller', .... )
AND ...
UNION ALL
SELECT 'CUSTOMER' as "CONTACT_TYPE", name1, name2, name3, score() as "SCORE"
FROM "CUSTOMERTABLE"
WHERE CONTAIN ("NAME1", 'Müller', .... )
AND ...

That way you'll get the results from both tables, the score-factor for each hit and an indicator for the contact type.

The latter will be useful if you want to use the found entries and do further lookups etc.

Concerning the join syntax (although you really don't need a join here), your command is missing the join condition:

SELECT *
FROM 
    "TABLE_A" A join "TABLE_B" 
     on a.id = b.id
    and a.date = b.date
WHERE 
a.id = 10;

The join condition is in the "on" line and the following "and" line(s).

This is not to be confused with the WHERE clause!

Your SQL statement lacks the join ("on") condition, that's why you get the error.

- Lars

[Former Member · Accepted solution]

Hello Lars,

thank you for your fast answer.

I have written and executed the following SQL-Statement:

SELECT SCORE() AS score, name1, name2, strasse
FROM LIEFERANTENSTAMMTABELLE
WHERE CONTAINS(name1,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(name2,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(name3,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(name4,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(ort,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(ortsteil,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(strasse,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
ORDER BY score DESC
UNION ALL 
SELECT SCORE() AS score, name1, name2, strasse
FROM KUNDENSTAMMTABELLE
WHERE CONTAINS(name1,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(name2,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(strasse,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
ORDER BY score DESC

But this SQL-Statement DOES NOT WORK, ONLY the following SQL-Statement works:

SELECT name1, name2, strasse
FROM LIEFERANTENSTAMMTABELLE
WHERE CONTAINS(name1,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(name2,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(name3,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(name4,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(ort,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(ortsteil,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(strasse,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
UNION ALL 
SELECT name1, name2, strasse
FROM KUNDENSTAMMTABELLE
WHERE CONTAINS(name1,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(name2,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))
OR CONTAINS(strasse,'Müller',FUZZY(0.7,'bestMatchingTokenWeight=0.7,textSearch=compare'))

Can you please tell me, why I can not use my "score"-Function:( ?

Thank you and best Regards,

Michael