Portal

Hlo Saara ,

When you create a string parameter the default length is 128. I think that's the issue here, the parameter is nullified if the provided string does not fit the expected format.

Can you increase the param size to 2000 or 4000 and give it a new try ?

you can use IN instead of Like 

example- SEM_NUMBER_TO_CHAR(ID)  in sem_f_split(:SEARCH_PARAM_IDS, ',')

Just a small suggestion :-  

For search form SemQL condition  if you had millions of records, the solution using LIKE operator or using sem_f_split plus a conversion would NOT scalable. 

Here is the function which return numbers only.

create or replace function mdm.sem_split_to_bigints(p_list text, p_del text DEFAULT ','::text) 

 RETURNS SETOF bigint 

 LANGUAGE sql  

STABLE SECURITY DEFINER

 AS $function$    

 select cast(unnest as bigint) from unnest(string_to_array(p_list, p_del)) where mdm.isbigint(unnest); 

$function$ ;  

create or replace function mdm.isbigint(string text) returns bool  

language plpgsql security invoker 

as $function$

 begin  

 perform string::bigint;  

 return true; 

exception when invalid_text_representation then   return false; 

end; 

$function$ ;   

select mdm.sem_split_to_bigints('1,2,3,AZ,456.2', ',')  -- return only numbers

Then use  ID in sem_split_to_bigints(:SEARCH_PARAM_IDS, ',')   in SemQL Condition of form search it  would be lightning fast.

Thanks for letting us know that it works.

Have a nice day.