View Criterias provide useful mechanism to extend where clause in your SQL query. Most of the cases can be provided using wizards. But, as real life shows, wizard is only a wizard. It doesn't allow you to put an inline query inside. What if such query needs to be parametrized by a binded variable? Using wizard i've created the sample view criteria:
Let's go to the source (or view object structure if you like) to give the answer for that kind of the questions.
Please select ViewCriteriaItem node and go to its Property Inspector.
To put an SQL query to the view criteria you need to do two things. First you need to change IsSqlFragment flag to true. Second is to write an SQL query in Value attribute. Please remember about putting it into brackets.
If you need to parametrize the query with bind variable, you need to set two additional flags. When you set IsBindVarValue to true, the BC4J framework will treat the value as a bind variable. I won't paste the screenshot from the wizard after leaving GenerateIsNullClauseForBindVars flag with its default. This flag needs to be set to false to avoid SQL comparison Values attribute value with null.
Of course the bind variable needs to be defined as in default, wizard way. Screenshot from the view object's source:
After changing values using Property Inspector, please be aware that the wizard may change flag values and whole query won't work as you expect.
Very useful tutorial! Here is a example using Java API:
ReplyDeleteViewCriteria vc = this.createViewCriteria();
ViewCriteriaRow vcRow = vc.createViewCriteriaRow();
ViewCriteriaItem vcItem =
vcRow.ensureCriteriaItem("DocumentId");
vcItem.setOperator(JboCompOper.OPER_IN);
vcItem.setIsSqlFragment(true);
vcItem.setValue("select 1 from dual");