6 Replies Latest reply on Oct 25, 2017 6:54 AM by Jose Aróstegui

    applyViewCriteria using an OR clause

    Jose Aróstegui

      Hi experts,


      I'm using Jdev 12.2.1.0.0 and I'd like to know how to add a viewCriteria to a VO using an OR clause instead the default AND.

       

      Currently I'm using function applyViewCriteria (vc, true).

       

      I've tried:

      vc.setConjunction(ViewCriteria.VC_CONJ_OR);

      before doing the applyViewCriteria  without success.


      Best regards,

      Jose.

        • 2. Re: applyViewCriteria using an OR clause
          Timo Hahn

          Can you describe in detail what you try to do?

          Do you want to 'or' two criteria?

          Or do you want to 'or' two attributes?

          Do you want to do it declarative or programmatically?

           

          May be a sample (using the HR schema) can help.

           

          Timo

          • 3. Re: applyViewCriteria using an OR clause
            Jose Aróstegui

            Hi Timo, thanks for your help....

             

            I'd need a where statement like this:

             

            WHERE 1=1
            AND ( (att1 like '%ES%' OR att1 like '%PT%'  OR att1 like '%IT%')      OR
                  (att2 like '%US%' OR att2 like '%ARG%' OR att2 like '%BRA%')    OR
                  (att3 like '%RUS%' OR att3 like '%THA%' OR att3 like '%JAP%')  )
            

             

            I'm doing it programmatically with this code:

             

              public void setZoneCriteria(HbtProductsVOImpl productVo, String regionAtt) {
                    String searchText = (String) ADFUtils.getBoundAttributeValue(regionAtt);
                    if (searchText != null) {
                        ViewCriteria vc = productVo.createViewCriteria();
                        vc.setName(regionAtt + "VC");
                     
                        searchText = searchText.substring(1, searchText.length() - 1); /*search Text will have a string: [ES,PT,IT]*/
                        List<String> countries = Arrays.asList(searchText.split(","));
                        for (int i = 0; i < countries.size(); i++) {
                            ViewCriteriaRow vcr = vc.createViewCriteriaRow();
                            ViewCriteriaItem vci = vcr.ensureCriteriaItem(regionAtt);
                            vci.setOperator(JboCompOper.OPER_LIKE);
                            vci.setValue("%" + countries.get(i).trim() + "%");
                            vc.addRow(vcr);
                        }           
                        productVo.applyViewCriteria(vc, true);                       
                    } else {
                        productVo.removeApplyViewCriteriaName(regionAtt + "VC");
                    }
                }
            

            Which is called several times:

              

            setRegionCriteria(productVo, "EuropeZone");
            setRegionCriteria(productVo, "AmericaZone");
            setRegionCriteria(productVo, "MEAZone");
            productVo.executeQuery();
            

             

            So... as OR clause is used by default, I can create dynamically this view criteria with OR clause without problems:

             

            (att2 like '%US%' OR att2 like '%ARG%' OR att2 like '%BRA%')
            

             

            But my problem is when I try to join them, it's always joined with and AND, like this:

             

            WHERE 1=1
            AND ( (att1 like '%ES%' OR att1 like '%PT%'  OR att1 like '%IT%')      AND
                  (att2 like '%US%' OR att2 like '%ARG%' OR att2 like '%BRA%')    AND
                  (att3 like '%RUS%' OR att3 like '%THA%' OR att3 like '%JAP%')  )
            

             

            Best regards,

            Jose.

             

            Ps: Is there any bug with the Advanced Editor in Chrome? It is scrolling up while I'm typing, so it's impossible to answer directly in the browser...

            • 4. Re: applyViewCriteria using an OR clause
              Timo Hahn

              Answer for the editor bug: Chrome editor issue-Scrolling upwards while typing

               

              For the real question, I'll need some time to look into it.

               

              Timo

              • 5. Re: applyViewCriteria using an OR clause
                Timo Hahn

                It looks like you create multiple ViewCriteriaRow and add them to one ViewCriteria. However, if you want to OR the ViewCriteriaRow in the ViewCriteria, you have to set the conjuction in the ViewCriteria to ViewCriteriaComponent.VC_CONJ_OR like

                vc.setConjuction(ViewCriteriaComponent.VC_CONJ_OR);

                before adding the rows.

                 

                Timo

                • 6. Re: applyViewCriteria using an OR clause
                  Jose Aróstegui

                  Hi Timo,


                  The ViewCriteriaRow in the ViewCriteria are "OR-ded" by default, so it's ok for me.


                  I need the ViewCriteria's to be "OR-ded" and not "AND-ded".


                  I've tried with:

                   

                  vc.setConjuction(ViewCriteria.VC_CONJ_OR);
                  

                  and

                  vc.setConjuction(ViewCriteriaComponent.VC_CONJ_OR);
                  

                  before

                  productVo.applyViewCriteria(vc, true);                       
                  

                   

                  ...with no success.

                   

                  BR,

                  Jose.