Hi All,
Database Version: Oracle Database XE 11g
Requirement: Let us consider the Employees table present in the HR Schema of the Oracle Database XE 11g. In the Employees there we have a column named HIRE_DATE. Now my requirement is to write a query where I will pass two bind variables for the HIRE_DATE column called startDateBind and endDateBind at runtime. Based on these value it will fetch me the output.
The sample query is as below:
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID from employees where hire_date>=:startDateBind and hire_date<=:endDateBind;
The above query prompt me to enter startDateBind (say 04-APR-2006) and endDateBind (say 23-MAY-06), and based of these inputs we got the output as below, which is absolutely fine.
But now the twist is what will happen in case I pass only one value either startDateBind or endDateBind. Below should be the output.
1. If I provide both startDateBind and endDateBind, it should return me the output in between these two date values, both startDateBind and endDateBind inclusive.
2. If I provide only startDateBind and endDateBind as null , it should return me the output which will be more than equal to the provided startDateBind value.
3. If I provide only endDateBind and startDateBind as null , it should return me the output which will be less than equal to the provided endDateBind value.
4. If I provide startDateBind and endDateBind both as null , it should return me all the records present in the table
Can anyone help me to write the SQL query for the above requirement fulfilling all the above four points.
Thanks & Regards,
Susanto Paul