sqloracle-sqldeveloperoracle-apexoracle-apex-5.1oracle-apex-5

Oracle SQL ignores page item in a criteria, when it is null


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;

Solution

  • 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;