I have an Interactive grid:
select emp_id,emp_name,dept,bu,start_date,end_Date;
On top of grid i have item fiters like P1_EMPNAME,P1_DEPT etc.
I need to add an Active button/list/checkbox which when clicked shows active employees on basis of start and end date .. So employees with End_date <SYSDATE should be inactive…
I tried adding a virtual column –Active– in the query but again how do i create and active filter on top on its basis..
select emp_id,emp_name,dept,bu,start_date,end_Date, case when end_date<sysdate then 'N' else 'Y' end as active ;
How can this be achieved?
Filtering is done in the WHERE clause, not in SELECT statement’s column list. It is OK to display someone’s status using CASE expression you wrote, but – that’s not enough.
One option is to use your current query as a source, and then apply filter as
WITH
temp
AS
(SELECT emp_id,
emp_name,
dept,
bu,
start_date,
end_date,
CASE WHEN end_date < SYSDATE THEN 'N' ELSE 'Y' END AS active
FROM some_table)
SELECT *
FROM temp
WHERE ( dept = :P1_DEPT
OR :P1_DEPT IS NULL)
AND ( emp_name = :P1_EMPNAME
OR :P1_EMPNAME IS NULL)
AND active = :P1_RB_ACTIVE;
presuming that P1_RB_ACTIVE represents a radio button with two values: Y and N.
2
There are 2 possible approaches to this. The first one is the one documented by @Littlefoot above. The idea behind is to change the query on which the IG is rendered.
A 2nd possibility is to use the API method APEX_IG.ADD_FILTER (docs). This will use the original query on the IG, but then apply a filter just as a user is doing it ( the user can then remove the filter if he wants to).