Skip to Content
avatar image
Former Member

Can we use contains with apply_filter

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 29, 2015 at 05:40 AM

    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
       filtered_users = APPLY_FILTER ("DEVDUDE"."CUSERS", :filter);
       select * from :filtered_users;
    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Karthikeyan,

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

      I believe your using the call statement as below


      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


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