CAP Java Query Builder API - How to excute HANA functions/procedures?

[christoffer_fuss]

Hi,

We are using CAP Java and HANA Cloud. We can succesfully execute simple SQL statements by using the Query Builder API but we have some problems with more complex queries, for example:

1. How can we execute a query with "count distinct". I can add a distinct() function but this is not working in the count function:
   

   Select.from(Apicalls_.class)
         .distinct()
         .columns(c -> func("TO_DATE", c.Date()).as("date"), 
                  c -> func("COUNT", c.UserId()).as("count"))
         .groupBy(g -> func("TO_DATE", g.Date()))
         .orderBy(o -> func("TO_DATE", o.Date()).asc())

2. How can we execute the HANA Fuzzy search methods?
3. How can we execute HANA functions or procedures?

I find many examples for Node.js but the examples for CAP Java are always very simple SQL statements....
Can anybody help with some examples?

Best Regards,

Chris

[former_member41752]

Hi all,

I have the following problem. My SQL like this:

SELECT TO_DECIMAL(SCORE(),3,2) score, T0.title as "title"<br>FROM MY_BOOKSHOP_BOOKS T0 WHERE CONTAINS(T0.title, 'olbia city', FUZZY(0.7,'similarCalculationMode=substringsearch'))<br>order by SCORE() DESC;

How can I build it with CQL statements in Java? I don't know how to execute a query with "order by SCORE() DESC".

Select.from(Books_.class) .where(e -> booleanFunc("CONTAINS", Arrays.asList(CQL.get(Books.TITLE).asRef(), val("olbia city"), plain("FUZZY(0.7, 'similarCalculationMode=substringsearch')"))))

Best regards,

Lihua

[former_member41752]

I have solved it with subquery.