> For the complete documentation index, see [llms.txt](https://docs.cloudio.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.cloudio.io/creating-datasources/working-with-datasources/creating-a-data-source/defining-a-pre-query/examples-of-pre-query-scripts.md).

# Examples of pre-query scripts

1. The manipulation of the WHERE clause of a Search. The platform appends the result of this query to the result of the generated whereClause.

```
// check if whereClause already exists
if (whereClause.length() > 0) {
    // whereClause already exists. append AND or OR clause as required
    whereClause.append(" AND ");
}
// use bind variables. e.g. ? or :EVENT_STATUS
whereClause.append("x.EVENT_STATUS = ?");
// append the bind variable
params.add(101);
```

 2\. Advanced manipulation of the WHERE clause

```
String custNo = data.get("accountNumber");
// check if the custNo is entered by the user in the search region
if (custNo != null) {
    if (whereClause.length() > 0) {
        whereClause.append(" AND ");
    }
    if (custNo.indexOf("%") != -1) {
        // wildchar found, use LIKE instea
        whereClause.append("X.ACCOUNT_NUMBER LIKE ?");
    } else {
        whereClause.append("X.ACCOUNT_NUMBER = ?");
    }
    params.add(custNo);
}
```

 3\. Calling a PL/SQL procedure before executing the query

```
/ Get the required parameter values from the current search criteria
// provided by the user i.e. user entered values in the search panel
String customerNameDummy = data.get("partyName");
String customerNumberDummy = data.get("accountNumber");
String contactNameDummy = data.get("contactName");
String emailDummy = data.get("emailAddress");
String postalCodeDummy = data.get("postalCode");

// Prepare the model to be used invoke the Pre-Query PL/SQL Procedure
RPCModelData model = new RPCModelData();
model.set("customerName", customerNameDummy);
model.set("customerNumber", customerNumberDummy);
model.set("contactName", contactNameDummy);
model.set("contactEmail", emailDummy);
model.set("postalCode", postalCodeDummy);
model.set("searchCriteria", searchValue);
// Invoke the Pre-Query PL/SQL Procedure. This will call
// PACKAGE.INSERT_ROW Procedure
db.postObjectToDB("customerPreQueryAPI", model);
// Get the return value
Double searchValue = model.get("searchCriteria");
// Use the return value in the where clause
if (searchValue != null) {
    if (whereClause.length() > 0) {
        whereClause.append(" AND ");
    }
    whereClause.append("x.search_context_id = ? ");
    params.add(searchValue);
}
```

 4\. The manipulation of the ORDER BY clause of a search. The platform appends the result of this query to the result of the generated order by clause.

```
if (orderByClause.length() > 0) {
  orderByClause.append(", ");
}
orderByClause.append("x.NET_LOSS_AMOUNT");
```

 5\. Replacing the FROM clause of a query

```
if (someCondition) {
fromClause.setLength(0); // remove the existing FROM CLAUSE
fromClause.append(" FROM NEW_TABLE_OR_VIEW_NAME x"); // alias should always be x
}
```

 6\. Manipulating the bind variable

```
// make sure to uncheck the "Query Allowed" property for this attribute
// so that the system won't include this attr in the system generated
// where clause
String phoneNo = data.get("phoneNumber");
if (phoneNo != null) {
    if (whereClause.length() > 0) {
        whereClause.append(" AND ");
    }
    whereClause.append("x.transposed_phone_number LIKE ?");
    params.add(new StringBuilder(phoneNo.replaceAll("[^0-9]", "")).reverse().append("%").toString());
}
```

 7\. Replacing the query with a custom query with UNIONs

```
Double custAccountId = data.get("customerId");
if (custAccountId != null) {
    // backup the existing bind variables if any as we need the repeat
    // the where clause of every UNION
    List paramsCopy = new ArrayList();
    paramsCopy.addAll(params);
    StringBuffer newWhereClause = new StringBuffer();
    if (whereClause.length() > 0) {
        newWhereClause.append(" AND ");
    }
    // backup the select clause as we need to repeat for every UNION
    String sel = selectClause.toString();
    newWhereClause.append("x.bill_customer_id = ?");
    /**
     ** RA_TOTAL_ROWS will help the platform to know the total rows
     * thereby avoiding the use of an extra query to calculate the total
     * rows i.e. select count(1)
     **/
    fullSQL.append("SELECT X.*, count(1) over() RA_TOTAL_ROWS FROM (");
    fullSQL.append(sel).append(fromClause).append(" WHERE ").append(whereClause).append(newWhereClause);
    params.add(custAccountId);
    fullSQL.append(" UNION ");
    newWhereClause.setLength(0);
    if (whereClause.length() > 0) {
        newWhereClause.append(" AND ");
    }
    newWhereClause.append("x.ship_customer_id = ?");
    fullSQL.append(sel).append(fromClause).append("WHERE ").append(whereClause).append(newWhereClause);
    params.addAll(paramsCopy);
    params.add(custAccountId);
    fullSQL.append(" UNION ");
    newWhereClause.setLength(0);
    if (whereClause.length() > 0) {
        newWhereClause.append(" AND ");
    }
    newWhereClause.append("x.customer_id = ?");
    fullSQL.append(sel).append(fromClause).append("WHERE ").append(whereClause).append(newWhereClause);
    params.addAll(paramsCopy);
    params.add(custAccountId);
    fullSQL.append(") X");
    if (orderByClause.length() > 0) {
        fullSQL.append(" ORDER BY ").append(orderByClause.toString().toLowerCase().replaceAll("_", ""));
    }
}
```

 8\. Restricting blind query by appending the following code at the end of the pre-query script

```
if (whereClause.length() == 0) {
  throw new RAException("Blind Query", "You must specify atleast one search criteria");
}
```

 9\. Query allowed By default, all attributes are query allowed and the platform automatically generates whereClause when a user perform a search on an attribute. To programmatically control the whereClause, uncheck the Query Allowed property while [defining the attribute properties](/creating-datasources/working-with-datasources/creating-a-data-source/defining-attribute-properties.md) to ignore those attributes while generating the whereClause. 

 10\. Custom handling of grid filters

You can access the data related to the grid filters through [FilterConfig](http://dev.sencha.com/deploy/gxt-2.2.5/docs/api/com/extjs/gxt/ui/client/data/FilterConfig.html) object. You can use`data.getFilterConfigs()` method to retrieve the list of FilterConfig.

```
import com.extjs.gxt.ui.client.data.FilterConfig;

List<filterconfig> filterConfigs = data.getFilterConfigs();
if (filterConfigs != null) {
    for (FilterConfig filter : filterConfigs) {
      if ("item".equals(filter.getField()) && filter.getValue() != null) {
        whereClause.append(" AND x.ITEM LIKE ?");
        params.add(((String) filter.getValue()).toUpperCase() + "%");
      }
    }
}
```

&#x20;11\. Range query

By default, platform generates range search fields in a simple search panel for all date and number attributes. For custom search panel, while [defining the attribute properties](/creating-datasources/working-with-datasources/creating-a-data-source/defining-attribute-properties.md), you can define calculated from and to attributes with **Select** and **Query Allowed** unchecked. This will limit the range for search values.

```
Date from = data.get("dateFrom");
Date to = data.get("dateTo");
if (from != null) {
    // check if whereClause already exists
    if (whereClause.length() > 0) {
        // whereClause already exists. append AND or OR clause as
        // required
        whereClause.append(" AND ");
    }
    // use bind variables. e.g. ? or :EVENT_STATUS
    whereClause.append("x.ORDER_DATE >= ?");
    // append the bind variable
    params.add(from);
}
if (to != null) {
    if (whereClause.length() > 0) {
        whereClause.append(" AND ");
    }
    whereClause.append("x.ORDER_DATE <= ?");
    params.add(to);
}
```

&#x20;12\. Query by name

The following pre-query script shows a simple approach for performing search:

```
boolean showAll = "Y".equals(db.getProfile("SHOW_ALL_ACTIVITIES"));
// from a given customer... show all activities for that customer
if (whereClause.indexOf("CUST_ACCOUNT_ID") == -1) {
    String group = data.get("groupCode");
    String dueBy = data.get("dueBy");

    if ("MY".equals(group)) {
        if (whereClause.length() > 0) {
            whereClause.append(" AND ");
        }
        whereClause.append("x.OWNER_ID = ?");
        params.add(userId);
    } else if ("ME".equals(group)) {
        if (whereClause.length() > 0) {
            whereClause.append(" AND ");
        }
        whereClause.append("x.CREATED_BY = ?");
        params.add(userId);
    }

    if (data.get("creationDate") != null) {
        if (whereClause.length() > 0) {
            whereClause.append(" AND ");
        }
        whereClause.append("x.CREATION_DATE > ?");
        params.add(data.get("creationDate"));
    }
    Date fromDate = new Date();
    Calendar c = Calendar.getInstance();
    c.setTime(fromDate);
    c.roll(Calendar.DATE, false);
    fromDate = dateUtils.getEnd(c.getTime());
    Date toDate = dateUtils.getEnd(new Date());
    Date yestDate = dateUtils.getStart(new Date());

    String statusCode = "Closed";
    if (dueBy != null) {
        if (whereClause.length() > 0) {
            whereClause.append(" AND ");
        }
        if ("YESTERDAY".equals(dueBy)) {
            whereClause.append(" x.status_code != ? AND x.PLANNED_END_DATE < ?");
            params.add(statusCode);
            params.add(yestDate);
        } else {
            whereClause.append("x.PLANNED_END_DATE > ? AND x.PLANNED_END_DATE < ?");
        }
        if ("TODAY".equals(dueBy)) {
            params.add(fromDate);
            params.add(toDate);
        } else if ("TOMORROW".equals(dueBy)) {
            c.setTime(fromDate);
            c.roll(Calendar.DATE, true);
            fromDate = c.getTime();
            params.add(fromDate);
            c.setTime(toDate);
            c.roll(Calendar.DATE, true);
            toDate = c.getTime();
            params.add(toDate);
        } else if ("THIS WEEK".equals(dueBy)) {
            c.setTime(fromDate);
            c.add(Calendar.DAY_OF_WEEK, c.getFirstDayOfWeek() - c.get(Calendar.DAY_OF_WEEK));
            fromDate = c.getTime();
            params.add(fromDate);
            c.add(Calendar.DAY_OF_YEAR, 7);
            toDate = c.getTime();
            params.add(toDate);
        } else if ("NEXT WEEK".equals(dueBy)) {
            c.setTime(fromDate);
            c.add(Calendar.DAY_OF_WEEK, c.getFirstDayOfWeek() - c.get(Calendar.DAY_OF_WEEK));
            c.add(Calendar.DAY_OF_YEAR, 7);
            fromDate = c.getTime();
            params.add(fromDate);
            c.add(Calendar.DAY_OF_YEAR, 7);
            toDate = c.getTime();
            params.add(toDate);
        }
    }

    if (!showAll) {
        if (whereClause.length() > 0) {
            whereClause.append(" AND ");
        }
        whereClause
                .append("(x.GROUP_ID IN (SELECT G.GROUP_ID FROM JTF_RS_GROUP_MEMBERS M, JTF_RS_GROUPS_B G, jtf_rs_roles_vl RL, jtf_rs_role_relations RR")
                .append(" WHERE M.GROUP_ID = G.GROUP_ID AND G.START_DATE_ACTIVE < SYSDATE AND NVL(G.END_DATE_ACTIVE, SYSDATE+1) > SYSDATE AND G.ATTRIBUTE1 = 'Y' AND M.DELETE_FLAG = 'N' AND RR.ROLE_ID = RL.ROLE_ID AND RR.role_resource_type = 'RS_GROUP_MEMBER' AND RR.delete_flag = 'N' AND M.RESOURCE_ID = ?")
                .append(" AND RR.ROLE_RESOURCE_ID = M.GROUP_MEMBER_ID AND RL.ROLE_CODE = 'GWT_MANAGER')")
                .append(" OR x.GROUP_ID IN (SELECT G.GROUP_ID FROM JTF_RS_GROUP_MEMBERS M, JTF_RS_GROUPS_B G WHERE M.GROUP_ID = G.GROUP_ID AND G.ATTRIBUTE1 = 'Y' AND G.ATTRIBUTE2 = 'Y' AND M.RESOURCE_ID = ?)")
                .append(" OR x.OWNER_ID = ? OR x.CREATED_BY = ?")
                .append(" OR EXISTS (SELECT 1 FROM xxdaz_mace_subscriptions XMS WHERE  XMS.USER_ID = ? AND XMS.ACTIVITY_ID = X.ACTIVITY_ID AND XMS.SUBSCRIBED_FLAG = 'Y'))");
        params.add(db.getUserSession().getResourceId());
        params.add(db.getUserSession().getResourceId());
        params.add(userId);
        params.add(userId);
        params.add(userId);
    }
}
```