Business Objects Query Builder Functions: Max, Min, and Sub-Queries

If you have spent some time with Business Object’s Query Builder you may have arrived at the conclusion that the SQL that it supports is rather simplistic. I don’t blame you, when I first started using Query Builder I felt the same was after spending just a few hours struggling with the tool. Most of the QB queries that I could find on the Internet were rather simplistic and they left me thinking that the tool was much more limited than it really is.

Case In Point: Getting the Functionality Max and Min Functions and Sub-Queries

If you have used SQL with databases you no doubt know that the functions MAX and MIN are quite popular and powerful. Logically they return the maximum or minimum value of a particular field. In Query Builder the functionality is a little different, but maybe in a good way. When this functionality is invoked all of the records with the maximum or minimum value for the particular field are returned.

The following syntax excerpt was swiped from our “Business Objects Query Builder Guide“, let’s review it and continue our discussion:

MAX and MIN and Much More
The ALL operator can be quite powerful and a bit confusing. It allows you to get as close to a sub-query, or MAX and MIN functions, as is possible with Query Builder.
The ALL operator should only be used between two properties or fields that are of the same data type. ALL can be used to identify the maximum or minimum value when used in conjunction with two of the same properties. When used with different fields or properties it can be used to compare property 1 to all possible values of property two.

Operator Syntax
= ALL
!= ALL
> ALL
>= ALL
< ALL
<= ALL

Examples
SELECT * FROM CI_InfoObjects WHERE si_children >= ALL si_children
(returns all info objects with the maximum number of children in the repository)

SELECT * FROM CI_InfoObjects WHERE si_id != ALL si_parentid
(finds all info objects that are not parents of other objects)

Query Builder MAX and MIN Functionality, not Functions

OK, busted, you got me. Their are no exact MAX and MIN functions that you drop into a SELECT clause. HOWEVER, in your WHERE clause all that you need to get the functionality is to combine the >= and <= operators with the special "ALL” operator. This operator essentially launches a sub query for all values of the valid and same data type property which follows it. Well, you get the point.

Didn’t I Say Something About Using a Sub-Query in Query Builder?

If the operators >= and <= result in MAX and MIN functionality when compared with the same property then what might happen if we try other operators or even different properties on both sides of the "ALL" operator. The various possible combinations are numerous, but you must remember that the compared properties must be of the same data type. I also believe that you are limited to valid properties of the same logical table, for example you cannot compare report properties to universe properties because they come from different logical tables (ci_InfoObjects and ci_AppObjects). Some of the combinations will produce interesting results, such as those given in the last example above.

I invite you to play with these and share your thoughts, discoveries, or comments here in the comments of this post. I also recommend that you take a look at our newly published "Business Objects Query Builder Guide", it probably is "The Most Complete Business Objects XI Query Builder Guide Ever Written" and it will help you to discover and master the secrets of Business Object's Query Builder, such as the one discussed in this article.