How to Use a SQL query using Groovy Script in Oracle Fusion

 How to Use a SQL query using Groovy Script in Oracle Fusion

In this post , We will be discuss about how to build a SQL query using Groovy Script in Oracle Fusion. We can use extract the data and compare the data in oracle fusion using groovy script in oracle fusion. This is the standard method in oracle fusion to extract and compare the data using groovy script in oracle fusion. here below is the complete steps to use a SQL query using Groovy Script in Oracle Fusion.

Steps to Use a SQL query using Groovy Script in Oracle Fusion

The example script on OpportunityVO shows a filter using multiple criterias on the same attribute. For example a search with a condition like 'where value > 20 and value < 30'

or Opportunity Name starting with 'A' need to be used.  On adding multiple conditions to same attribute using ensureCriteriaItem() was not working as expected.

The below sample script can be plugged to any event where Opportunity records of Name that starts with 'A' or 'AMMM' needs to be retrieved. It is not possible to use ensureCriteriaItem() for setting multiple conditions on the same field. When using like this, the last criteria that got applied will be considered. Instead of using two view criteria, we can create two ViewCriteriaRow on the same ViewCriteria.

println("Start ensureCriteriaItem test")
def vo = newView('OpportunityVO');
def vc = vo.createViewCriteria();
def vcRow = vc.createViewCriteriaRow();
def vcRow1 = vc.createViewCriteriaRow();
def vc1 = vcRow.ensureCriteriaItem("Name");
vc1.setOperator("STARTSWITH");
vc1.setValue("A");
def vc2 = vcRow1.ensureCriteriaItem("Name");
vc2.setOperator("=");
vc2.setValue("AMMM");
vcRow.setConjunction(1);
vc.insertRow(vcRow);
vc.insertRow(vcRow1);
vo.applyViewCriteria(vc);
vo.executeQuery();
while(vo.hasNext()){
println("inside while")
def row = vo.next()
println(row.getAttribute('Name'))
}
println("End ensureCriteriaItem test")