sqloracle-databaselivelink

If-else for null condition SQL


I am going to create a livereport using three example inputs:

  1. Document Start Date
  2. Document End Date
  3. Emp ID

These are 3 rows in the SQL table DocumentData.

What I want to reproduce is the following:

If the user does not put any value in "Emp ID" field, it should show all "Document Start Date" and "Document End Date". Otherwise, only values corresponding to that "Emp ID" should be shown.

It may be very simple but I still need the query.


Solution

  • Perhaps this could be a solution:

    -- should work perfectly
      select *
      from DocumentData
      where 
        case 
          when :emp_id is null then 1 -- attention :emp_id introduced in stead of emp_id
          when emp_id = :emp_id then 1
          else 0
        end = 1
    

    The :emp_id should be the variable that comes from the application (the field that is being altered by the user). The 1 in the case when statement acts as a true condition.