Query at ReportExtract

You have an employee table, and user will give department number and from which you need to fetch out the respective employees.

select *
from employees
where department_id in (
select distinct department_id
from departments
where department_id = '&DepID'
)

But, twist is, if department table do not have any records, query should return all employee records.

How will you do it? below is the solution

create table departmentsBKP
as
select * from departments

select *
from employees
where department_id in (
select distinct department_id
from departmentsBKP
where department_id = '&DepID'
)
union
select *
from employees
where 0 = (select count(*) from departmentsBKP)

delete from departmentsBKP

and now again if u fire select statement, you will get all records of employees table.