OnDemand Users Group

Support Forums => ODWEK, Java and REST APIs => Topic started by: pankaj.puranik on December 31, 2012, 03:45:35 PM

Title: AND and OR search criteria.
Post by: pankaj.puranik on December 31, 2012, 03:45:35 PM
I see that it is possible to use either AND or OR condition.
I have a case where I want to do a search as 'Brand = XYZ AND account number = 2345 OR hhNumber = 678.

Is it not possible to do it in one go? Or I would have to break the query into two and then merge the results?

Thanks
Pankaj.
Title: Re: AND and OR search criteria.
Post by: Alessandro Perucchi on January 02, 2013, 03:13:17 PM
Hello Pankaj,

Well if you use the method ODFolder.setOrSearchCriteria(boolean), then the whole search criteria will be either

A AND B AND C AND ...

or

A OR B OR C OR ...

with ODFolder.search().

If you want to do something more complex than "everything AND" or "everything OR", then you will need to use the method

ODFolder.search(String), ODFolder.search(String,  ODCallback), ODFolder.search(String, String, String), ODFolder.search(String, String, String, ODCallback), ODFolder.seach(String, String, String, String) or ODFolder.search(String, String, String, String, ODFallback), then you can do anything you want with SQL queries.

Hope that helps a bit :-)

Sincerely yours,
Alessandro
Title: Re: AND and OR search criteria.
Post by: jfunk on December 05, 2013, 05:40:17 PM
Good day,

It depends on whether each of the columns is an individual index or not.  If hhNumber is an index and either Brand or account number is an index, then you may want to break this up with a UNION clause.  That is because in DB2 one of two conditions can occur.  The first is index nullification, i.e. table space scan.  The second is a multi-index join.  The first is not palitable while the second may not be.  I would test this first with SQL separate from OnDemand determining what occurs and the performance.  If the UNION is faster, then using the Keyboard option, inject the query using the UNION.

Jeff