Skip to Main Content


Faceted Search

OraDev16Aug 17 2022 — edited Aug 17 2022

Hi All,
I've the following setup for Faceted Search:
create table fs_test(
id number,
city varchar2(200)
insert into fs_test values(1,'Tokyo');
insert into fs_test values(2,'Tokyo,Berlin');
insert into fs_test values(3,'Nairobi,Tokyo,Helsinki');
insert into fs_test values(4,'Denver');

-- classic report query
select id, city, case when instr(city,',')>0 then 'Multiple City' else 'Single City' end "No. of Cities"
from fs_test
where ((:P2_CITY is not null and lower(:P2_CITY) in
Select regexp_substr(lower(city)
,Level) city
From dual
Connect By regexp_substr(lower(city)
,level) is not null
)) or :P2_CITY is null)

-- Faceted Search criteria
Name : P2_CITY
Type : Checkbox Group
List of Values
Type : SQL Query
SQL Query:
select distinct trim(regexp_substr(city,'[^,]+',1,level)) d, trim(regexp_substr(city,'[^,]+',1,level)) r
from fs_test
connect by regexp_substr(city,'[^,]+',1,level) is not null
order by 1;
List Entries:
Compute Counts : Enabled
Show Counts : Enabled
Zero Count Entries : Show Last
Sort by Top Counts : Enabled
Issue :
Wherever there are multiple cities; those city names are appearing in Faceted Search Checkbox Group; but without record count and the checkbox is disabled.
Expectation :
We should be able to do searching based on City irrespective of whether it belongs to a single or multi city record.
Thank you

Post Details
Added on Aug 17 2022