Filtering data through multiple rows

Lets say we have created a form on the table EMP and want to filter the data, so that only the Employees from Department 20 and 30 are displayed. The best UI for the user is a filter-block, where you see all departments and can mark them.

In this filter we click on the checkboxes for department 20 and 30:



The button Query data (Abfrage starten) starts a query on the emp-block.



What do we need for this technique?

-) A CONTROL-block with an item TI_Filter_String (char, 2000)
-) A checkbox CB_Filter in the DEPT-block (numeric, 0-unchecked, 1-checked)

The button, which starts the query, needs a WHEN-BUTTON-PRESSED:

:Control.TI_Filter_String := ';';
go_block ('DEPT');
first_record;
LOOP
  IF :DEPT.CB_Filter = 1 THEN
    :Control.TI_Filter_String := :Control.TI_Filter_String || :DEPT.DEPTNO || ';';
  END IF;
  IF :SYSTEM.LAST_RECORD = 'TRUE' THEN 
    EXIT;
  ELSE
    Next_Record;
  END IF;
END LOOP;
go_block ('EMP');
execute_query;

The EMP-block needs this default-where:

:Control.TI_Filter_String LIKE '%;' || DEPTNO || ';%'

The trick is, that each deptno from the filter-string can be found using the LIKE %;...;% - technique:



Have fun with this little trick!
Gerd