Create fulltext index for a View

[Former Member]

I created an attribute view with a column consisting of two concatenated strings. My aim is to search this view with fuzzy search.

First I got this error:

Failed to execute action: Error: $.hdb.Connection.executeQuery: SQL error. NR: 7, ERROR: feature not supported: columns of (var)binary and LOB types need fulltext index: line 1 col 187 (at pos 186) at ptime/query/checker/check_expr.cc:5713

Afterwards I tried to create the mentioned fulltext Index with the following query:

CREATE FULLTEXT INDEX DISPLAYNAMEINDEX ON "data::UserSearchView" ( DISPLAYNAME )

Resulting in this error:

Could not execute 'CREATE FULLTEXT INDEX DISPLAYNAMEINDEX ON "data::UserSearchView" ( DISPLAYNAME )'
SAP DBTech JDBC: [7]: feature not supported: cannot create index on view: line 1 col 43 (at pos 42)

Is it somehow possible to create the index anyways? And/or what do I need to do to use fuzzy search in this scenario?

[Former Member · Accepted solution]

Hey guys,

first of all thank you for your answers.

My attribute view looks like this

My calculation column is just the concatenation of the first- and the lastname

"FIRSTNAME" +' '+ "LASTNAME"

But if the concatenation is not fuzzy searchable I need to add a DISPLAYNAME Column to the user table. Or is there a better way?

Kind regards,

Marc

[lbreddemann · Accepted solution]

Why don't you just apply the CONTAINS condition on both first name and surname column but only display the displayname?

[Former Member · Accepted solution]

But if my search string contains first- and lastname and I apply the CONTAINS condition on the firstname and then on the lastname neither of them would find a match or am I wrong?

e.g.:

my search String is "Max Mustermann"

with

CONTAINS("FIRSTNAME",?,FUZZY(0.8))

I would find anyone called max

even with

CONTAINS("FIRSTNAME",?,FUZZY(0.8)) OR CONTAINS("LASTNAME",?,FUZZY(0.8))

I wouldn't find him

[lbreddemann · Accepted solution]

That's not what I meant.

You can do

SELECT ... WHERE CONTAINS((col1, col2, col3), 'term1 term2 term3', FUZZY(0.7)) ...;

This should allow for your scenario.

See The CONTAINS() Predicate - SAP HANA Search Developer Guide - SAP Library

[Former Member · Accepted solution]

Thank you very much, this solution works great for me

[Former Member]

Hi,

I tried this and ended up with same error.

I believe this is a limitation with contains predicate as it works only on column store tables (simple tables and join-views) as mentioned in developer guide.

So it seems like contains predicate cannot work on calculated columns.

Regards

-Anil

[lucas_oliveira]

Hello Marc,

Certainly the second option is not going to happen as you can see from the error message.

You can certainly set search capabilities for a column at the attribute view level using the Search Options' panel in Studio.

However, it seems you trying to use calculated column for your scenario. If so, I don't think that's possible. At least in a small example I made here I was not able to do so.

Maybe you can explain your scenario a little further (underlying table and attribute view details) so we can try to help.

BRs,

Lucas de Oliveira