I am trying to implement "IN clause" operator in view object and this implementation should be done without usage of setQuery(),getQuery() and addElement(),setWhereClause(),addWhereClause() methods as per our coding guidelines.
The ViewObject has one viewCriteria which has a bind variable which accepts comma separated string value for IN clause.
I tried this in two ways
- 1) Overriding the public String getCriteriaItemClause(ViewCriteriaItem vci) in VOImpl.java file.
Before override, changed the bind variable to 'IN clause' by custom operator , so now my bind variable has IN clause operator.
@Override public String getCriteriaItemClause(ViewCriteriaItem vci) { String rv= super.getCriteriaItemClause(vci); String vcName=vci.getViewCriteria().getName(); if("mscHvGopDistributionVOCriteria".equals(vcName)){ String operatorName=vci.getOperator(); String voAttributeName=vci.getAttributeDef().getName(); if("IN".equals(operatorName)){ boolean vcForRowMatch=vci.getViewCriteria().getRootViewCriteria().isCriteriaForRowMatch(); String tableColumnName=vci.getColumnNameForQuery(); if(vcForRowMatch){ rv="IN ("+ vci.getSearchValue()+"))"; } else{ rv= tableColumnName +" IN (SELECT regexp_substr("+vci.getSearchValue()+" ,'[^,]+', 1, level) from dual connect by level <= regexp_count ("+vci.getSearchValue()+", ',')+1)"; } } } return rv; } |
And appended the viewCriteria to VO and query looks like as below.
SELECT * FROM (
*********
) QRSLT WHERE ( ( x IN (SELECT regexp_substr('52:10000','1091:10005' ,'[^,]+', 1, level) from dual connect by level <= regexp_count ('52:10000','52:10005' ',')+1)) )
But it is throwing an error- java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: x.
The x is an alias for column.
2) Directly adding the IN and regular expression to the View object in ViewCriteriaItem taq as below.
<ViewCriteriaItem Name="X" ViewAttribute="X" operator="IN" Conjunction="AND" Value=":x" UpperColumns="0" ValidateBindVars="true" IsBindVarValue="true" Required="Required"> <!-- bind variable :values is a string of comma-separated values like "C1,C2,C3" --> <CompOper Name="InValues" Oper="IN" ToDo="1" MinCardinality="1" MaxCardinality="1"> <TransientExpression><![CDATA[ def values = adf.object.getValue(); // returns ":x" as a string which is included in the generated SQL return "IN (" + "select regexp_substr(${values}, '[^,]+', 1, level) " + "from dual connect by regexp_substr(${values}, '[^,]+', 1, level) is not null)" // string of comma-separated values parsed using SQL functions ]]></TransientExpression> </CompOper> </ViewCriteriaItem> |
appended the viewCriteria to VO and query looks like as below.
SELECT * FROM (
*********
) QRSLT WHERE (( X IN (SELECT regexp_substr(:x ,'[^,]+', 1, level) from dual connect by level <= regexp_count (:x ',')+1)))
This query fetches no rows as it seems for inner query bind variable ‘x’ is not getting set.
Appreciating any help.
Thanks in advance