Custom Operator ( IN operator ) In viewCritria at Design Time

There are a couple of post which explains how to apply IN operator where clause to viewobject. This post refer to already existing solution and new posted solution on case by case basis.  Here goes the different approaches  :

1. IN OPERATOR THROUGH CUSTOM VC -> When u have option to write a method, you can create and apply custom view criteria as described in here ( Use SQL subquery and SQL OPERATOR ( IN, BETWEEN) in ViewCriteria ).
2. IN OPERATOR IN VO QUERY -> When you have IN operator in query of viewobject and want to pass multiple values through that bind variable, you can use either of these approaches :
 a) Using Type and Cast : Example 126 from  Steve's Blog :-

•       Create Type and DB function returning that type for comma separated list.
•      Use type in query like

SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
FROM DEPARTMENTS Departments
WHERE DEPARTMENT_ID IN (

SELECT * FROM TABLE( 

CAST ( in_number_list(:CommaSeparatedListOfDeptId) 

as num_table)))

 b) Using regular expression : Passing comma separated string as bind variable for VO query's IN operator 
          WHERE Emp.ENAME in
                (select regexp_substr(:Bind_Ename_Comma_Sep_List,'[^,]+', 1, level)
                 from dual
                 connect by
                      regexp_substr(:Bind_Ename_Comma_Sep_List, '[^,]+', 1, level)
                          is not null)


 3. OVERRIDE getCriteriaItemClause METHOD FOR CUSTOM WHERE CLAUSE :
Using bind variable for the SQL statements with IN clause
Download the application from the link ( available at bottom of page) and look for Dept_OverriddenGetCriteriaItemClause_VOImpl.java .



4. USING CUSTOM IN OPERATOR IN VIEW CRITERIA AT DESIGN TIME :

At first create you design time viewcriteria. Regarding how to add custom operator you can refer to dev guide Section 31.3.3. Here goes the code snippet for CUSTOM_IN Operator.


NOTE : Default value for departmentIdVar is -1to avoid where clause i.e. DepartmentId IN ( null).



If you want to have some other kind of customized where clause that should be applied using custom operator, write a method in your VOImpl returning whereClause String and refer to it in your customOperator . For example : you want to apply where clause : -   Salary Between 2000 And 5000 the your customWhereClauseMethod should return String "Between 2000 And 5000".

return  adf.object.getViewCriteria().getViewObject().customWhereClauseMethod()

You can download the sample application from here ( CustomOperatorApp ) . Run the application module and try passing the comma separated values and it works.

Use SQL subquery and SQL OPERATOR ( IN, BETWEEN) in ViewCriteria

Use SQL subquery and SQL OPERATOR ( IN, BETWEEN) in ViewCriteria

This post will illustrate how to use IN,BETWEEN operators in ViewCriteria. How to use the SQL subquery with ViewCriteria.

In this application, HRAppModuleImpl.java contains a method applyCustomCriteria() with three input parameters - departmentIdVar, lowSalaryVar, and highSalaryVar. Expose this method and test .

Below steps explains the code in snapshot below-

1.Create ViewCriteria using createViewCriteria().

2.Create ViewCriteriaRow using createViewCriteriaRow().

3.Create String with required where Clause.

4.Create ViewCriteriaItem  using ensureCriteriaItem. 

5.Use setOperator() to set the sqlOperator. Use JboCompOper. class to find list of supported operators.

6.Use setValue() method of ViewCriteriaItem to set value . Incase of IN/BETWEEN operator, add multiple values. For sql subquery for IN operator, simply use setValue.

8.Use addElement method to ViewCriteriaRow to VC.

9.Use applyViewCriteria to apply created viewCriteria.

 Expose Method -

Run AM and pass parameters to method -

Project can be downloaded from here.