When my page loads the page item :P2136_LEAVE_TYPE is null. i have this query
WITH Emp_Leaves AS
(select ID,
EMPLOYEE_ID,
LEAVE_YEAR,
LEAVE_TYPE,
LEAVE_DAYS,
REMAINING_DAYS,
CLOSED
from LEAVES_PERSONEL
where "EMPLOYEE_ID" = :P2136_EMPLOYEE_ID
and REMAINING_DAYS > 0 and CLOSED = 0
order by LEAVE_YEAR, LEAVE_TYPE)
select ID,
EMPLOYEE_ID,
LEAVE_YEAR,
LEAVE_TYPE,
LEAVE_DAYS,
REMAINING_DAYS,
CLOSED
FROM Emp_Leaves;
i want when the user change the value in my page item :P2136_LEAVE_TYPE and its not null to use it as a criteria in my query like this:
WITH Emp_Leaves AS
(select ID,
EMPLOYEE_ID,
LEAVE_YEAR,
LEAVE_TYPE,
LEAVE_DAYS,
REMAINING_DAYS,
CLOSED
from LEAVES_PERSONEL
where "EMPLOYEE_ID" = :P2136_EMPLOYEE_ID
**and LEAVE_TYPE = :P2136_LEAVE_TYPE**
and REMAINING_DAYS > 0 and CLOSED = 0
order by LEAVE_YEAR, LEAVE_TYPE)
select ID,
EMPLOYEE_ID,
LEAVE_YEAR,
LEAVE_TYPE,
LEAVE_DAYS,
REMAINING_DAYS,
CLOSED
FROM Emp_Leaves;
You should also test for NULL, so that the condition is true if one of the cobditions is true
WITH Emp_Leaves AS
(select ID,
EMPLOYEE_ID,
LEAVE_YEAR,
LEAVE_TYPE,
LEAVE_DAYS,
REMAINING_DAYS,
CLOSED
from LEAVES_PERSONEL
where "EMPLOYEE_ID" = :P2136_EMPLOYEE_ID
and (LEAVE_TYPE = :P2136_LEAVE_TYPE* OR LEAVE_TYPE IS NULL)
and REMAINING_DAYS > 0 and CLOSED = 0
order by LEAVE_YEAR, LEAVE_TYPE)
select ID,
EMPLOYEE_ID,
LEAVE_YEAR,
LEAVE_TYPE,
LEAVE_DAYS,
REMAINING_DAYS,
CLOSED
FROM Emp_Leaves;