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
    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.

    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

      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