Monday, July 8, 2013

Joining Static WHERE Clause and View Criteria

There might be cases when usage of declarative ADF BC View Criteria features will be not enough to implement complex filtering, as for example see my previous post - Bug in View Criteria - Joining Multiple Criteria Item Groups Doesn't Work. I will describe in this post how you can reuse complex WHERE clause and use ADF View Criteria to capture user criteria params only.

The trick is described in this sample application - CustomCriteriaOperatorApp_v3.zip. Along with View Criteria type - non required Bind Variables, I have defined one marked as required (WHERE type). Bind Variable - regionIdVar is used directly from static WHERE clause (marked as Required):


Static WHERE clause can be complex one, and usually there is no option to define complex WHERE clause with only declarative features offered by View Criteria.

Make sure to set Hide flag for required Bind Variable, otherwise it will be rendered as required criteria field in ADF Query:


We need to capture criteria parameter from ADF Query and initialize Bind Variable, this can be done through transient attribute defined in VO. There is no need to passivate this attribute explicitly, because framework will take care and passivate bind variable value automatically:


This sample defined WHERE clause with two separate groups connected with AND. Second group is using bind variable - regionIdVar:


Here you can see how bind variable is initialized, it will get value from transient RegionId field rendered by ADF Query:


Other bind variables are defined as View Criteria type, will be appended to WHERE clause dynamically on runtime:


RegionId is retrieved from ADF Query and set for static WHERE clause, while FirstName criteria value is appended dynamically:

No comments: