Custom Search with SEM_F_SPLIT only works with 10 or less parameters
S
Saara.vainikainen
started a topic
6 months ago
Hi,
I have created a Custom Search Form that uses the SEM_F_SPLIT function. If I search with a list of 1 to 10 or 11 values it works perfectly, but after that the results seem to not be filtered at all.
Does someone have an idea why this could be happening?
Best Answer
T
Toshish Chauhan
said
6 months ago
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.
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.
S
Saara.vainikainen
said
6 months ago
Thanks! Why didn't I think of that. Now it works!
I am using IN as the condition, tried with LIKE originally but it gave me errors. (The ID's we are looking contain both numbers and letters and even some other characters but good to know.)
Saara.vainikainen
Hi,
I have created a Custom Search Form that uses the SEM_F_SPLIT function. If I search with a list of 1 to 10 or 11 values it works perfectly, but after that the results seem to not be filtered at all.
Does someone have an idea why this could be happening?
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.
- Oldest First
- Popular
- Newest First
Sorted by PopularToshish Chauhan
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.
Saara.vainikainen
Thanks!
Why didn't I think of that. Now it works!
I am using IN as the condition, tried with LIKE originally but it gave me errors.
(The ID's we are looking contain both numbers and letters and even some other characters but good to know.)
Subham Dixit
Thanks for letting us know that it works.
Have a nice day.
-
Extend a model with new entities or attributes
-
Data types in xDM
-
Effective date on entities
-
Search using wild cards
-
Export a model from production and import on a development environment
-
"Allow Delete" vs "Allow Removal" privileges
-
LOV label in Named Query
-
Select location on a map and save coordinates
-
Is there a way to set up a master-detail relationship on browse mode?
-
Choose Either a Stepper or A Workflow Based on The User Privileges
See all 270 topics