wrong results in SearchEngine

Hello,

i try to find a duplicate person in So Online. I had a sample record in database:

firstname: Luekas

lastname: Stapel

zipcode: 50999

I write an crmScript to check before import, if the new person is duplicate in the  databae:

#setLanguageLevel 3;

String vorname = "Lükas";
String nachname = "Hein";
String plz = "50999";

SearchEngine sePlz;
sePlz.addField("person.person_id");
sePlz.addField("person.firstname");
sePlz.addField("person.lastname");
sePlz.addCriteria("person.(address->owner_id).zipcode","equals", plz, "and", 1);

if ((vorname != vorname.substitute("ü","ue").substitute("ö","ue").substitute("ä","ae")) || (vorname != vorname.substitute("ue","ü").substitute("oe","ö").substitute("ae","ä"))) {
//add criteria with umlaut ä,ö,ü and without umlaut
sePlz.addCriteria("person.firstname","equals", vorname.substitute("ü","ue").substitute("ö","ue").substitute("ä","ae"), "or", 2);
sePlz.addCriteria("person.firstname","equals", vorname.substitute("ue","ü").substitute("oe","ö").substitute("ae","ä"), "and", 2);
} else {

sePlz.addCriteria("person.firstname","equals", vorname, "and", 2);
}
//add Lastname
sePlz.addCriteria("person.lastname","equals", nachname, "and", 3);
//Execute
sePlz.execute();
//PrintOut
while (!sePlz.eof()) {
print(sePlz.getField(0) + " " + sePlz.getField(1) + " " + sePlz.getField(2));
sePlz.next();
}

if i execute that i get the following result:

firstname: Luekas

lastname: Stapel

But it is wrong the lastname "Stapel" ist different to "Hein". so i should get an empty result. Is there any error in addCriteria?

 

RE: wrong results in SearchEngine

Hi Mike!

I can't see anything immediately wrong with your script, and I tried executing it after inserting a couple of Lukases into my test environment and it seems to work fine?

If you instead of executing the script you can do "sePlz.buildSql();" to generate what we think the SQL output will look like (this isn't always 100%, but it's most likely the correct view).

Look at the order of the logical operators, is built like you intended?

I tried with and without the left join for zipcode and added the zipcode for the two Lukases, but it still gave me the correct result.

Av: Hans Wilhelmsen 2. jan 2020

RE: wrong results in SearchEngine

Hi Hans,

thanks for your reply.

Here ist the generated SQL Code, i think its should be work.

select a0.person_id,a0.firstname,a0.lastname
from person a0
left join address a1 on (a1.owner_id = a0.person_id)
where ((a1.zipcode = ?) and ((a0.firstname = ? or a0.firstname = ?) and ((a0.lastname = ?))))

But if i execute that, i get the wrong result. Is it possible to see the sql script with correct values and not with placeholders?

thanks

Mike

Av: Mike Behrendt 2. jan 2020

RE: wrong results in SearchEngine

Hi Hans,

 

i change the criteria, now it works, but can you explain me why?

 

#setLanguageLevel 3;

String vorname = "Lükas";
String nachname = "Hein";
String plz = "50999";

SearchEngine sePlz;
sePlz.addField("person.person_id");
sePlz.addField("person.firstname");
sePlz.addField("person.lastname");


if ((vorname != vorname.substitute("ü","ue").substitute("ö","ue").substitute("ä","ae")) || (vorname != vorname.substitute("ue","ü").substitute("oe","ö").substitute("ae","ä"))) {
//add criteria with umlaut ä,ö,ü and without umlaut
sePlz.addCriteria("person.(address->owner_id).zipcode","equals", plz, "and", 0);
sePlz.addCriteria("person.firstname","equals", vorname.substitute("ü","ue").substitute("ö","ue").substitute("ä","ae"), "and", 0);
sePlz.addCriteria("person.lastname","equals", nachname, "or", 0);

sePlz.addCriteria("person.(address->owner_id).zipcode","equals", plz, "and", 1);
sePlz.addCriteria("person.firstname","equals", vorname.substitute("ue","ü").substitute("oe","ö").substitute("ae","ä"), "and", 1);
sePlz.addCriteria("person.lastname","equals", nachname, "and", 1);
} else {
sePlz.addCriteria("person.(address->owner_id).zipcode","equals", plz, "and", 0);
sePlz.addCriteria("person.firstname","equals", vorname, "and", 0);
sePlz.addCriteria("person.lastname","equals", nachname, "and", 0);
}
//add Lastname

print(sePlz.buildSql());
//Execute
sePlz.execute();
//PrintOut
while (!sePlz.eof()) {
print(sePlz.getField(0) + " " + sePlz.getField(1) + " " + sePlz.getField(2));
sePlz.next();
}

 

so it works and i get the right result.

 

best regards

 

Mike

Av: Mike Behrendt 2. jan 2020