Skip to Content

CAPM: Fuzzy search and use of view from custom handler on HANA


Dear Colleagues,

I'm trying to use the fuzzy search functionality in my CAP project and tried to do the following:

1) Build query using

let query = cds.parse.cql("SELECT from BLOCKS { *, TO_DECIMAL(SCORE(),3,2) as score } WHERE CONTAINS(TEXT,'TargetText',FUZZY(0.8,'textSearch=compare,andThreshold=0.3'))");

But looking at internal function _handleContains() I see that fuzzy is not supported, only LIKE statement.

2) I've created .hdbview

VIEW BLOCKSFUZZY (in SEARCH NVARCHAR(5000)) AS SELECT BLOCK_ID 
FROM BLOCKS
WHERE CONTAINS(RAWTEXT, :SEARCH, FUZZY(0.8,'textSearch=compare,andThreshold=0.3'));

And corresponding entity in schema and service

schema.cds

entity BlocksFuzzy(SEARCH : String) {
    key block_ID : UUID @odata.Type;
}

service.cds

entity BlocksFuzzy(SEARCH: String) as select from BlocksFuzzy(SEARCH: :SEARCH) {*};

With these I've managed to have an OData entity set that I can request using and it is working

../BlocksFuzzy(SEARCH='TargetText')/Set

Now I try to use this functionality in one of my custom handlers and I cannot execute select on the parametrized entity:

SELECT.from(BlocksFuzzy).where({SEARCH: {"=": 'Text'}})
SELECT.from("BLOCKSFUZZY('TargetText')")
and etc..

Please provide a possible solution in this case.

I would really appreciate any input on how to use HANA fuzzy search in CAP.

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Jul 17, 2020 at 01:50 AM

    Hi Semyon,

    If you already found out that fuzzy search is not supported in cds fluent api, then you could just use reqular HANA SQL commands like below:

    db.run(`SELECT "${this.sequence}".NEXTVAL FROM DUMMY`)

    Taken from my blog below where I provided a work around since DB sequence is not supported yet.

    https://blogs.sap.com/2020/07/04/cap-using-hana-db-sequence/

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.