Using IN operator on VOs

Using IN operator on VOs

31. Using IN operator on VOs
Issue: IN operator is not part of the default operators available on the VOs. But, based on a particular requirement, this might be needed to be implemented using the existing objects
Example Scenario: Display the full employee details in tabular format from the multi-select EmployeeLOV's selection. Here the multi-select LOV's selected values are available as a list and are passed as an input to the EmployeeVO. The EmployeeVO should filter and show only those records from the selected list.
Options Tried: NA
Resolution:
There are 4 different ways to achieve this
a. Create custom IN operator on the VO and return the desired string declaratively: This is applicable only when the bind variable takes a single value and the IN operator checks for a nested condition/hard-coded values. This can be used for EO based VOs.
b. Create custom IN operator on the VO and return the desired string programatically: This can be done by overriding the getCriteriaItemClause. The user should note that the replacement value should not result in erroneous query

@Override
public String getCriteriaItemClause(ViewCriteriaItem vci) {
if (vci.getAttributeDef().getName().equals("DepartmentName") &&
vci.getViewCriteria().getName().contains("DeptSampleVC")) {
if (vci.getViewCriteria().getRootViewCriteria().isCriteriaForQuery()) {
return getINClauseForDatabaseUse(vci);
} else {
return getINClauseForCache(vci);
}
} else {
return super.getCriteriaItemClause(vci);
}
}

protected String getINClauseForDatabaseUse(ViewCriteriaItem vci) {
String bindVarValue = getCommaDelimitedDeptNames();
String bindVarName = "CommaDelimitedDeptNames";
String whereCluase = "1=1";
if (bindVarValue != null && bindVarValue.trim().length() != 0) {
whereCluase =
this.getEntityDef(0).getAliasName() + ".DEPARTMENT_NAME IN (select /*+ CARDINALITY(A, 50) */ * from TABLE (cast (in_list_char ( :" +
bindVarName + " ) as ChartableType))A)";
}
return whereCluase;
}

protected String getINClauseForCache(ViewCriteriaItem vci) {
 String whereCluase = "1=1";
 return whereCluase;
}

This can be used for EO based VOs.
c. Override the VO query to include the IN operator in the where clause

SELECT EMP_NO, EMP_NAME, DEPT_NO
FROM EMPLOYEES
WHERE EMP_NO IN (
  SELECT regexp_substr(:QueryEmpNo,'[^,]+', 1, LEVEL) items
  FROM DUAL
  CONNECT BY REGEXP_SUBSTR(:QueryEmpNo, '[^,]+', 1, LEVEL) IS NOT NULL
)

QueryEmpNo is a bind variable of type String. This is a comma separated list of EmpNos
The VO needs to be an expert mode. I found this to be performant and easier to adopt for read-only cases. Other options could be used for the editable VOs.
d. Add a function to the schema accepting  a comma-separated string argument and returning a NUM_TABLE as its result. This allows for the bind variable to be of String type.
The VO then accesses the temporary table to check the IN values
Function in DB:

CREATE TYPE num_table AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION in_number_list (p_in_list  IN  VARCHAR2)
RETURN num_table
AS
l_tab   num_table := num_table();
l_text  VARCHAR2(32767) := p_in_list || ',';
l_idx   NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := to_number(TRIM(SUBSTR(l_text, 1, l_idx - 1)));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN l_tab;
END;
/

VO Query:

SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
Departments.MANAGER_ID,
Departments.LOCATION_ID
FROM DEPARTMENTS Departments
WHERE DEPARTMENT_ID IN (SELECT * FROM TABLE( CAST ( in_number_list(:CommaSeparatedListOfDeptId) as num_table)))

The CommaSeparatedListOfDeptId is a bind variable of type String
This case cannot be used if the developer does not have permissions/is not allowed to create functions in DB
e. Set the where condition with the required string: This is the simplest, but needs to be used with caution as the where clause is being set/added programmatically. The user needs to take care of the other criteria, over writing, appending etc. This code can be added in bean/AM/VoImpl.

vo.setWhereClause("EMP_NO IN " + commaSeparatedList);

vo.addWhereClause("EMP_NO IN " + commaSeparatedList);

The following code snippet can be added in the respective method:

        if(!selectedRows.isEmpty() && selectedRows.size() > 0) {
            StringBuilder empList = new StringBuilder(" EMP_NO IN (");
            String empNo = null;
            for(int i=0; i < selectedRows.size(); i++) {
                Key selectedRowKey = (Key)selectedRows.get(i);
                Row row = employeeVO.findByKey(selectedRowKey, 1)[0];
               
                empNo = row.getAttribute("EmpNo");
                empList.append("'"+empNo + "' ,");
            }
           
            empList.replace(empList.lastIndexOf(","), empList.lastIndexOf(",")+1, "");
            empList.append(")");
           
            String whereClause = empList.toString();
            this.setWhereClause(whereClause);
            this.executeQuery();
        } 

Bonus:
Code to construct the comma separated list

StringBuilder empNoList = new StringBuilder();
            String delimiter = ",";
            for (Object e : selectedEmpNoList) {
                if (empNoList.length() > 0)
                    empNoList.append(delimiter);
                empNoList.append(e);
            }