8 Replies Latest reply: Nov 25, 2011 12:57 AM by newsmaker84 RSS

    Using In operator in view criteria

    newsmaker84
      Hi,

      We have a requirement to apply filter conditions to some of the view objects dynamically at run time.

      The conditions may use any of the operators such as =, >, >=, <, <=, in, not in etc.

      While we specifically use IN operator or NOT IN operator, we are getting a console log while applying the filter stating

      "WARNING: Invalid operator used for VCItem <<Attribute Name>> in VC __DefaultViewCriteria__".

      I tried the same with a sample application having a readonly vo in an AM and tested using ADF Model Tester.

      While specifying the view criteria condition, still I am getting the same warning message in the console.

      Anyhow, in both our actual application as well as in the sample, I am getting the desired result inspite of the warning.

      Now, my question is,
      is there something I have to do specifically to get rid of this warning or is it the default behaviour.

      In case if I have to do something, what is that?

      Thanks and Regards,
      Guru K
        • 1. Re: Using In operator in view criteria
          Timo Hahn
          Guru K,
          as far as I know there are no 'in' or 'not in' operators in view criteria. At least I can't see them in the wizard (11.1.1.4.0).

          Which jdev version you are using?

          Can you post the code how you set up the view criteria to use the 'in' operator?

          How does the resulting sql look like?

          Timo
          • 2. Re: Using In operator in view criteria
            newsmaker84
            Yeah, you are correct Timo. There is no provision to use IN operators in the view criterias we create at design time.

            But, still we can dynamically create view criteria rows with the required operators and operands.

            Find below my code:

            I have a service method available in AMImpl for public usage.

            public RowSet getFilteredRows() {
            ViewObjectImpl viewImpl = findViewObject("DepartmentsROView");
            ViewCriteria vc = viewImpl.createViewCriteria();
            vc.setName("TempViewCriteria");
            vcRow = new ViewCriteriaRow(vc);
            ViewCriteriaItem rowViewCriteriaItem = vcRow.ensureCriteriaItem("DepartmentId");
            rowViewCriteriaItem.setOperator(JboCompOper.OPER_IN);
            rowViewCriteriaItem.setValue(10);
            vc.addElement(vcRow);
            RowSet resultRowSet = (RowSet) viewImpl.findByViewCriteria(vc, -1, ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
            return resultRowSet;
            }

            This is some equivalent code which we are using in our application.

            In a sample, through tester (By running the application module), we can dynamically specify view criteria conditions.

            1. Create a view object and make it available in an application module.
            2. Run the application module to launch the tester.
            3. Select the view object in the tester to get the data (Show as Table option will be more convinient).
            4. Using the option "Specify View Criteria" (Binocular icon) available in top of the table, we can specify the conditions.
            5. Click on Find button to apply the conditions and filter the records accordingly.

            I used the condition IN (10, 20, 30) in the Departments table's DepartmentId attribute to filter the records and also to get the log in my console.

            Thanks and regards,
            Guru K
            • 3. Re: Using In operator in view criteria
              902003
              Hi,

              ViewCriteria viewCriteria =yourVO.createViewCriteria();
              ViewCriteriaRow criteriaRow = viewCriteria.createViewCriteriaRow();

              String varStr =
              "IN (" + "10"+
              ")";

              criteriaRow.setAttribute("YourAttributeName", varStr);
              viewCriteria.addElement(criteriaRow);
              this.yourVO.applyViewCriteria(viewCriteria);
              this.yourVO..executeQuery();

              try this...
              • 4. Re: Using In operator in view criteria
                newsmaker84
                Hi,
                Thanks for your update. I have tried that also. Ultimately, the final outcome is to apply a where clause using IN operator and both of our code stands good.

                Also, I do not want to get my original view object getting disturbed and so I didn't execute my query. Instead I used findByViewCriteria() API which works with a secondary rowset and will not disturb the actual VO collection.

                Thanks and regards,
                Guru K
                • 5. Re: Using In operator in view criteria
                  Timo Hahn
                  You can try Jobineshs solution http://jobinesh.blogspot.com/2010/12/using-oraclejbodomainarray-with.html
                  or mine http://tompeez.wordpress.com/2011/08/21/extending-viewcriteria-to-use-sql-contains-4/

                  Timo
                  • 6. Re: Using In operator in view criteria
                    ramandeep singh - oracle
                    This is good way to use in clause

                    http://jobinesh.blogspot.com/2010/09/using-bind-variable-for-sql-statements.html
                    • 7. Re: Using In operator in view criteria
                      John Stegeman
                      String varStr =
                      "IN (" + "10"+
                      ")";
                      Please don't do that... learn about bind variables.

                      John
                      • 8. Re: Using In operator in view criteria
                        newsmaker84
                        Thanks Tim/John/Ram.

                        Actually we are not supposed to create any objects in the database and also not supposed to define view criterias during design time.

                        We have at least 10 different combinations of filter conditions for a particular view object and there are around 600 such view objects. Those filter conditions are arrived only during runtime and we cannot predict all the required view crieterias for defining them at design time.

                        That is the reason why we opt for dynamic view criteria creation and getting filtered records from the secondory rowset.

                        Thanks and regards,
                        Guru K