How do I show all of the data on an interactive grid and then reduce it with shuttle filters on oracle_apex?

Question

I have 5 shuttle filters: Project, Section, Phase, Group, Old_New. I'll show one as an example:

Project Filter	Buttons	Selected Project(s)
Project1	<---
Project2	--->	Project2
Project3

As I currently have it, data will not display until until I have populated my filters, but I would like it to be the other way around. I'm think my where clause is what's wrong, but I don't know how to connect the filters to the query otherwise.

  WITH T 
    AS (
       SELECT md.ID,
              md.MATERIAL_NUMBER,
              md.MATERIAL_DESCRIPTION,
              md.PROJECT,
              md.SECTION,
              md.PHASE,
              md.GROUP,
              md.OLD_NEW
         FROM MATERIAL_DATA md

    /* P30 is the page my filters are on */
       WHERE  (:P30_PROJECT like '%' || md.PROJECT || '%' or md.PROJECT is NULL)
              AND (:P30_SECTION like '%' || md.SECTION || '%' or md.SECTION is NULL)
              AND (:P30_PHASE like '%' || md.PHASE || '%' or md.PHASE is NULL)
              AND (:P30_GROUP like '%' || md.GROUP || '%' or md.GROUP is NULL)
              AND (:P30_OLD_NEW like '%' || md.OLD_NEW || '%' or md.OLD_NEW is NULL)
       )
SELECT DISTINCT ID,
       MATERIAL_NUMBER,
       MATERIAL_DESCRIPTION,
       PROJECT,
       SECTION,
       PHASE,
       GROUP,
       OLD_NEW
  FROM T

The filters do work, but my interactive grid is an empty table until each filter has data populating the "selected" section. Thanks for the help. =)

Answer 1

My wager is that you really want something like

WITH T 
    AS (
       SELECT md.ID,
              md.MATERIAL_NUMBER,
              md.MATERIAL_DESCRIPTION,
              md.PROJECT,
              md.SECTION,
              md.PHASE,
              md.GROUP,
              md.OLD_NEW
         FROM MATERIAL_DATA md

    /* P30 is the page my filters are on */
       WHERE  (:P30_PROJECT = md.PROJECT or :P30_PROJECT is NULL)
              AND (:P30_SECTION = md.SECTION or :P30_SECTION is NULL)
              AND (:P30_PHASE = md.PHASE  or :P30_PHASE is NULL)
              AND (:P30_GROUP = md.GROUP  or :P30_GROUP is NULL)
              AND (:P30_OLD_NEW = md.OLD_NEW  or :P30_OLD_NEW is NULL)
       )
SELECT DISTINCT ID,
       MATERIAL_NUMBER,
       MATERIAL_DESCRIPTION,
       PROJECT,
       SECTION,
       PHASE,
       GROUP,
       OLD_NEW
  FROM T

but I don't know what exactly the values of your various page items are and what values you want returned. If each of the page items have a colon delimited list of values, you would want something more like

WITH T 
    AS (
       SELECT md.ID,
              md.MATERIAL_NUMBER,
              md.MATERIAL_DESCRIPTION,
              md.PROJECT,
              md.SECTION,
              md.PHASE,
              md.GROUP,
              md.OLD_NEW
         FROM MATERIAL_DATA md

    /* P30 is the page my filters are on */
       WHERE  (md.PROJECT member of apex_string.split(:P30_PROJECT,':') or :P30_PROJECT is NULL)
              AND (md.SECTION member of apex_string.split(:P30_SECTION, ':') or :P30_SECTION is NULL)
              AND (md.PHASE member of apex_string.split(:P30_PHASE, ':') or :P30_PHASE is NULL)
              AND (md.GROUP member of apex_string.split(:P30_GROUP, ':') or :P30_GROUP is NULL)
              AND (md.OLD_NEW member of apex_string.split(:P30_OLD_NEW, ':') or :P30_OLD_NEW is NULL)
       )
SELECT DISTINCT ID,
       MATERIAL_NUMBER,
       MATERIAL_DESCRIPTION,
       PROJECT,
       SECTION,
       PHASE,
       GROUP,
       OLD_NEW
  FROM T