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 <= ALLExamples
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.
Hello
How can I use the “UNION” in BO, I need to use it for two queries.
Best Regards.
Yes, many edit the SQL and just put it in. This is a technique that some use so that a query always returns data (and users don’t see the dreaded no data message). WebI also supports union natively in the UI, as I recall this late at night.
Can I use subquery as an object for the query like:
select col_a,(select sum(col_C) from table t where t.colb=z.col_b )
from
table Z
Hi Rose, I don’t think I have ever tried this, but I think it could work (as long as the subquery has only one field in the select clause). The object may fail to parse in Designer, but I can’t see why it wouldn’t work once used in a report. Please try it and report back. The object would have to be equal to the string “(select sum(col_C) from table t where t.colb=z.col_b )”, right? Be sure the data type matches the subquery’s output. Please do try it and report back with your findings.
Thanks Julian, but it doesn’t alow me to create a subquery.
Anyway I have another more important project and I am looking for a macro or utility that will help me look for any override sqls in reports and another utility to look for derived tables, and obejcts using subqueries in a Univere.
This is required for database conversion project, as we are converting all our UDB DB2 databases to Oracle. Can you please help.
Hi Rose, I don’t have any personal experience with any macros that can pull that kind of “classic” Business Objects metadata. The SDK can’t help directly because that meta data is not stored in the CMS InfoStore. However, I am not sure, perhaps “override SQL” is a property. I will look at this when I get a chance.
How to convert this webi formulainot deski
Variable #2 Name: var-Candidate Currently Referred
Variable #2 Formula: =Max([Candidate Is Currently Referred]) Where([Candidate Reference Date] = [var-Candidate Latest Reference Date])
Variable #1 Name: var-Candidate Latest Reference Date
Variable #1 Formula: =Max([Candidate Reference Date]) In([Candidate ID])
What I’m trying to accomplish is to do typical type 2 dimensional logic to grab the ‘current’ record giving a specific date (prompt):
Show me the current employee record at a specific point in time (prompted):
Pseudo SQL:
select MAIN.*
from EMPLOYEE MAIN
where MAIN.EFFDT = (select max(EFFDT)
from EMPLOYEE
where EMPLID = MAIN.EMPLID
and EFFDT <= $as_of_date)