Last Updated on(Last Updated On: January 22, 2018)
There is a scenario observed when using fuzzy search doesn’t bring up appropriate results that you have searched for. You will see the search based on keyword entered along with multiple non required rows.
Note: This wiki is for the cases when search is a single word and not multi-word.
Affects Version: ALL
Root cause of this fuzzy search related issue is only related if you are using rowNumber as ID in fuzzyDB configuration and this is being created using a method ROW_NUMBER().
The value of the RowNumber column is getting populated by function ROW_NUMBER().
And the cause of the issue is same i.e. the ROW_NUMBER() column populates the value dynamically which is resulting into unexpected results.
For better understanding refer the explanation below which describes how FuzzyDB works and what is happening in our case :
There are two steps :
Learn DB : All the data from the mapped table/view is fetched and indexes are created.
Fuzzy Search/Extraction : Then on fuzzy search those indexes are referred to get the Row Ids and the data from those rows is populated in the result set.
Check the row with RowNumber 798, it contains GRP0605 as the DocType_Code.
So, during learning FuzzyDB, GRP0605 is there at RowNumber 798 and so the 798 is stored in the indexes.
When we do FuzzySearch of the word GRP0605, the indexes give the 798 as id and on fetching the record at RowNumber 798, below query i.e. where RowNumber = 798 is executed. This resulted in a different record.
And the reason for the same is that RowNumber column is again getting calculated dynamically.
So a column whose value is getting populated using RowNumber() is not a right candidate for the Fuzzy ID.
The workaround would be create a custom column with the combination of ID’s of the joined table such that the value of this custom column is unique.
And then map that custom column as the Fuzzy Primary/Unique key.