I created a simple HR application with departments and Employees
In DepartmentsView i put query criteria, wanting departments that have Employees with old hire date.
The issue is that i want if no criterion is entered to show all departments. I thought since exist criterion is optional it should work. But no, it show anyway only departments that have Employees.
I think The solution would be easy for oracle developers if for view criteria where clause, instead of:
( (EXISTS(SELECT 1 FROM EMPLOYEES Employees WHERE ( ( ( Employees.HIRE_DATE < :inBeforeDate ) OR ( :inBeforeDate IS NULL ) ) ) AND (Departments.DEPARTMENT_ID = Employees.DEPARTMENT_ID))) )
when the exists criterion is optional to set:
( (EXISTS(SELECT 1 FROM EMPLOYEES Employees WHERE ( ( ( Employees.HIRE_DATE < :inBeforeDate ) OR ( :inBeforeDate IS NULL ) ) ) AND (Departments.DEPARTMENT_ID = Employees.DEPARTMENT_ID))) OR ( :inBeforeDate IS NULL ))
test case :
This is not a bug. The optional EXISTS view criteria item will "drop out" of the query only when all of the view criteria items in the nested view criteria drop out of the query. Optional View Criteria Items that reference literal values can "drop out" of the query when the literal value supplied by the user at runtime (in this case, in the search form) is null. However, at present, view criteria items that reference user-created bind variables always remain in the query. The solution is to change you nested view criteria to use a literal value instead of a bind variable. Using a literal value does not mean that the value is a CONSTANT value. At runtime ADFBC creates bind variables with temporary names whose values will be bound to the value of the literal operand. When I test changing your nested view criteria to use a literal operand instead of a user-created bind variable, it works as you're expecting.
ReplyDeleteThanks Steve, it seeps that bind variables create lots of problems, (Also cascading LOVs).So i come to conclusion that they should not be used when they are not realy needed.
ReplyDeleteHi Milkbird,
ReplyDeletein our project we had problems with the EXISTS clause in view criteria. Therefor we took your testcase to check why it works for you and not for us.
The reason is that there seems to be a bug if SQL-Mode is "Expert" and not "Normal".
I'll open an SR at metalink to check this.
regards
Peter