HANA CONTAINS() predicate with multiple columns and multiple search criteria

[BTP_Architect]

Hello,

I want to use contains() predicate with multiple search criteria.

But I have an error message saying: feature not supported: More than one CONTAINS with multiple columns

SELECT * FROM HANA_View
WHERE CONTAINS ( ( WERKS, MATNR, LANDX, MAKTX, NAME1, LAND1, LIFNR, IDNLF ) , '%Search1%') 
AND CONTAINS ( ( WERKS, MATNR, LANDX, MAKTX, NAME1, LAND1, LIFNR, IDNLF ) , '%Search2%') 

Could you please suggest how can I manage this requirement using one contains?

[sourin_acharjee]

Hi,

Please read into the documentation for CONTAINS function of HANA.

https://help.sap.com/docs/SAP_HANA_PLATFORM/691cb949c1034198800afde3e5be6570/1eb06e9afee74e7b8ebb117...

It clearly states that if you specify multiple CONTAINS conditions in WHERE, then only one of them can contain multiple columns!

[David21]

when using the contains function it works fine with one column but when i use multiple columns it gives me an error. This is the statement I am using:

query = cds.parse.cql(`SELECT * from Dummy WHERE contains((firstName,lastName), '${req.query.SELECT.search[0].val}')`);

Any ideas why it's giving an error ?

[pfefferf]

As it is mentioned in the official documentation here this is a restriction.

I'm wondering why you are using an "AND" between the two where conditions. Should this be an "OR", if yes, than you can overcome that limitation by defining two similar selects, except the different where clause, and combine the result using a UNION.

Regards,
Florian

[BTP_Architect]

Hello Florian,

The user will search for a material with two search criterea

Supposing that he wants materials code containing XX provided by vendor code containing YY

This have to be done by using AND in the select

My thinking was to use the INTERSECT of two select. What do you think?

Will it impact the performance?

Regards,

Moez.