I have an Interactive grid:
select emp_id, emp_name, joining_date from employees
where (:P1_EMP_NAME ='All' or :P1_EMP_NAME !='All' and emp_name=:P1_EMP_NAME);
On top of it i have a filter.
P1_EMP_NAME-> Select list->
select emp_name d, emp_id store from employees;
The EMP_NAME search is already configured.
I have FIND button which on click submits the P1_EMP_NAME item and refreshes grid..
I need to configure similar filter using joining_date: P1_JOINING_DATE.
But i want to understand how do i dispaly dates in item and how can i configure in query such that the date selected filter IG query accordingly?
3
A DATE has both date- and time-components so to match an instant use (changing the date format to your required format):
select emp_id, emp_name, joining_date
from employees
where ( :P1_EMP_NAME ='All'
or emp_name=:P1_EMP_NAME
)
AND ( :P1_JOINING_DATE IS NULL
OR joining_date = TO_DATE(:P1_JOINING_DATE, 'YYYY-MM-DD HH24:MI:SS')
);
or if your joining_date values always have a midnight time-component then:
select emp_id, emp_name, joining_date
from employees
where ( :P1_EMP_NAME ='All'
or emp_name=:P1_EMP_NAME
)
AND ( :P1_JOINING_DATE IS NULL
OR joining_date = TO_DATE(:P1_JOINING_DATE, 'YYYY-MM-DD')
);
If you want to match an entire day then use (again, changing the format to match your requirements):
select emp_id, emp_name, joining_date
from employees
where ( :P1_EMP_NAME ='All'
or emp_name=:P1_EMP_NAME
)
AND ( :P1_JOINING_DATE IS NULL
OR ( joining_date >= TO_DATE(:P1_JOINING_DATE, 'YYYY-MM-DD')
AND joining_date < TO_DATE(:P1_JOINING_DATE, 'YYYY-MM-DD')
+ INTERVAL '1' DAY
)
);
or:
select emp_id, emp_name, joining_date
from employees
where ( :P1_EMP_NAME ='All'
or emp_name=:P1_EMP_NAME
)
AND ( :P1_JOINING_DATE IS NULL
OR TRUNC(joining_date) = TO_DATE(:P1_JOINING_DATE, 'YYYY-MM-DD')
);
Note: this last option would not use an index on joining_date and would need a separate function-based index on TRUNC(joining_date).
Page items do not have a “data type” that maps to the database data types. All page items are strings in a browser context. A page item like “Number field” just ensures the browser renders the correct directives for a number field but it doesn’t really map to the NUMBER data type. The values that is passed from the html page is implicitely or explicitly casted to the correct datatype by the apex engine.
If you have a page item that contains a date, then it’s advised to do a TO_DATE(:P1_PAGE_ITEM,'<your_date_format') in the query to ensure that the bind variable is correctly interpreted as date.
9