Forum Stats

  • 3,872,050 Users
  • 2,266,372 Discussions


Faceted Search

OraDev16 Member Posts: 110 Red Ribbon
edited Aug 17, 2022 2:22PM in APEX Discussions

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



  • Stax
    Stax Member Posts: 184 Silver Badge

    i understood nothing 😚


    SQL> ed
    Wrote file afiedt.buf
      1  with fs_test(id, city) as (
      2  select 1,'Tokyo' from dual  union all
      3  select 2,'Tokyo,Berlin' from dual  union all
      4  select 3,'Nairobi,Tokyo,Helsinki' from dual  union all
      5  select 4,'Denver' from dual)
      6  select trim(regexp_substr(city,'[^,]+',1,level)) d, count(*) cc
      7  from fs_test
      8  connect by regexp_substr(city,'[^,]+',1,level) is not null
      9   and id=prior id
     10   and prior sys_guid() is not null
     11  group by trim(regexp_substr(city,'[^,]+',1,level))
     12* order by 1
    SQL> /
    D                              CC
    ---------------------- ----------
    Berlin                          1
    Denver                          1
    Helsinki                        1
    Nairobi                         1
    Tokyo                           3
  • OraDev16
    OraDev16 Member Posts: 110 Red Ribbon

    Hi Stax,

    Thanks for your quick response; please click the link below where I created the demo with Info as a region mentioning all the codes and expectations; Please let me know if you need some clarifications.

    Thank you.