ADF Query Panel with CheckBox (Boolean search attribute)

In database the Boolean values in a column are usally stored as Y and N. This example shows how to search such columns using CheckBox.

      1.       Add a new attribute in view object. Select Mapped to Column or SQL, unselect Selected in Query, provide the alias and SQL expression DECODE(ACTIVE,’Y’,1,’N’,null).

         2.       In the attribute control hints select control type as Check Box

       3.       In query tab create view criteria to use as search panel.

       4.       From data controls, drag and drop the view criteria as ADF Query panel with table.

        5.       Run the page and use the checkbox to search for active and inactive users.

      6.       You can also provide a default value for the checkbox in viewcriteria.

    

      7.       You can download the complete example here.

ADF View Object - View Criteria Row with SQL Literal

View Criteria provides an ability to decoratively add a where clause fragment to view object. 

Jdeveloper GUI by default provides option to declare a view criteria items value either as a 

1. Literal 
2. Bind Variable. 

There is also support for adding a literal that would be treated as a 

3. SQL fragment

Below steps show how to configure

      1.       Create View Criteria, Add a view criteria item, Select operator as HireDate, Operator as Between and Operand as Literal

       2.       Go to the Source Tab of the View Object and add an IsSqlFragment attribute with value true in view criteria item value as below.

          3.       From the View Criteria Editor provide values sysdate-3650 and sysdate

       4.       Click on Test button to validate the Query

       5.       To Test the Apply the View Criteria in Application Module Data Model.

        6.       Run The Query from SQL WorkSheet and AM tester to compare the results.

       7.       The Sample application attached here. Let me know if you have any questions.

       8.       You can also use the same logic for other operators; ViewCriteriaRow also supports the IsSqlFragment attribute. Here is an example.

Adding a IN clause to view object using programmatically view criteria

To apply a simple view criteria programmatically to view object you can use the steps from the ADF Developer guide. But when you need to create a view criteria item using IN clause, there is a additional step required. You need to set the min and max cardinality for the view criteria item value.

Below code demonstrates the necessary steps for using the criteria with a IN clause


        ViewObject vo = am.findViewObject("Employees");
        ViewCriteria vc =  vo.createViewCriteria();
        ViewCriteriaRow vcr =  vc.createViewCriteriaRow();
        ViewCriteriaItem vci =vcr.ensureCriteriaItem("EmployeeId");
        vci.setOperator(JboCompOper.OPER_IN);
        vci.setValueMinCardinality(3);
        vci.setValueMaxCardinality(3);
        vci.setValue(0, 101);
        vci.setValue(1, 102);
        vci.setValue(2, 103);
        vc.addRow(vcr);
        vo.applyViewCriteria(vc);

Download the complete example here.