Can we use contains with apply_filter

[Former Member]

hi  i need to perform a dynamic query for fuzzy search, i tried using contains query with apply_filter, it doesn't work . could anyone please let me know the correct way to achieve this.

[lbreddemann · Accepted solution]

Yes, that's possible:

create column table cusers as ( select * from users)

drop procedure flexfilt;

create procedure flexfilt (IN filter NVARCHAR(4000))
language SQLSCRIPT
reads sql data
as
begin

   filtered_users = APPLY_FILTER ("DEVDUDE"."CUSERS", :filter);
   select * from :filtered_users;
end;

call flexfilt ('CONTAINS (USER_NAME, ''DEVDUDE'', FUZZY(0.8) )' );   

USER_NAME	USER_ID	USER_MODE	EXTERNAL_IDENTITY	CREATOR	...
DEVDUDE	150679	LOCAL	?	SYSTEM	...
DEDUDE	220316	LOCAL	?	SYSTEM	...

Be aware that CONTAINS() doesn't work on subqueries, so you can only have a table, but not a subquery (or table variable) in the APPLY_FILTER for this scenario.

[Former Member · Accepted solution]

thank you, i am getting the following error when i try to use the same.

[lbreddemann · Accepted solution]

Looks like there is an issue with the filter condition you tried to use.

Please also post the CALL statement with the definition of the filter variable.

Maybe your table doesn't actually have a column named HANA (beware of upper/lowercase writing here, when using double quotes!)

[Former Member · Accepted solution]

Hi Karthikeyan,

I tried the similar thing and was facing the same issue.

I believe your using the call statement as below

call TEST_SEARCH('CONTAINS(PROCEDURE_NAME,"HANA", FUZZY(0.9))');

There should not be a double quote (") before and after search string 'HANA'.

Instead it should be a single quote(') escaped by another single quote (') which makes it look ''HANA''.

Therefore the call statement should be as below

call TEST_SEARCH('CONTAINS(PROCEDURE_NAME,''HANA'', FUZZY(0.9))');

Please try out and let me know if that was the problem.

Regards

-Anil