I am creating a query that involves a subquery (no problem - I know how to do that). But I want my subquery to be a combination (intersection) of 2 other queries. Business Objects greys out the “Combine Queries” button in the Query Panel when you are creating the subquery, so it won’t let me do it. Does anyone have any suggestions?
I am using Business Objects 4.1.3 and MS Access 97.
Thank you.
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-02-11 14:50:20 EST, you write:
I am creating a query that involves a subquery (no problem - I know how to
do that). But I want my subquery to be a combination (intersection) of 2 other queries. Business Objects greys out the “Combine Queries” button in the Query Panel when you are creating the subquery, so it won’t let me do it. Does anyone have any suggestions?
I am using Business Objects 4.1.3 and MS Access 97.
You cannot, unfortunately, use the Combine Query feature in a sub-query in BusinessObjects. However, there is a reason for this (other than they just didn’t feel like programming that feature ).
In MS Access you cannot do an Intersection in the database. MS Access only supports Union. Because of that, an Intersection or Minus operation is actually performed on the client.
You can verify this easily: create a query using Island Resorts that does a Union of Customers and Salespeople. Examine the SQL code by pressing the SQL button on the query panel. You will see one piece of SQL code generated with a UNION in the middle.
Now change your combined query operation from Union to Intersection and examine the query. There are now TWO queries. Each query data set will be downloaded to the client where the Intersection operation is performed.
The same thing happens with a Minus… most databases do not support this feature.
Now consider a sub-query: the sub-query is run first, and the resulting data set is used as criteria for the outer (main) query. In order to use a sub-query with an intersection against MS Access (or many other databases) here is what would have to happen:
Step 4 is, unfortunately, not possible. For that reason the combined query option is not available in a sub-query.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
You wrote:
I am creating a query that involves a subquery (no problem - I know how to do that). But I want my subquery to be a combination (intersection) of 2 other queries.
Business Objects has developed a new bit of software called “Set Analyzer” that will allow for set based analysis. In essence what it does is execute multiple SQL queries at once. I have seen it demoed and it seems to be able to do provide a response based on the results of two separate queries. Check out the BO website for add’l info.
Andreas Fleckenstein
Listserv Archives (BOB member since 2002-06-25)
In a message dated Tue, 15 Feb 2000 2:07:40 PM Eastern Standard Time, AFleckenstein@MICROS.COM writes:
You wrote:
I am creating a query that involves a subquery (no problem - I know how to do that). But I want my subquery to be a combination (intersection) of 2 other queries.
Business Objects has developed a new bit of software called “Set Analyzer” that will allow for set based analysis. In essence what it does is execute multiple SQL queries at once. I have seen it demoed and it seems to be able to do provide a response based on the results of two separate queries. Check out the BO website for add’l info.
And be sure you are sitting down when you see the cost for a license for the new product! 
If you thought BusinessObjects was expensive…
If you really need this functionality, there are other ways around it. If you can do the Intersection on your database platform (Oracle supports this, for example) then you can create a View to do the Intersection, and then use the View in your sub-query. Since the Intersection is not apparent to BusObj, it does not cause any problems.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)