Faceted Search

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)

              ,'[^,]+'

              ,1

              ,Level) city

    From dual

    Connect By regexp_substr(lower(city)

                ,'[^,]+'

                ,1

                ,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