Union Query

pbrad

Hi,

I have a situation where I have a client that has two independent business spaces that are unrelated. However, within each business space is an appointment business object. The appointment object in each case is identical and contains a reference to an advisor and a reference to a client within the appointment. The clients within the two business spaces are totally different but the advisors are the same advisors.

The client wants to be able to view all appointments across both business spaces in order to be able to see all appointments for a given advisor.

I have made an experimental bsv which has just the two appointment bo's as external bo's and I can query each of the external business objects. The sql statement in each case is:

SELECT TestAppt.Subject FROM UNION_TESTAPPT AS TestAppt

SELECT TestApptAFL.Subject FROM UNION_TESTAPPTAFL AS TestApptAFL

What I would like to do is essentially merge these two query results as they have exactly the same structure in two different tables. It seems that I need to use a MySQL Union query. Is this correct?

If so, what would the correct syntax be as I have had no luck with:

SELECT TestAppt.Subject FROM UNION_TESTAPPT AS TestAppt UNION SELECT TestApptAFL.Subject FROM UNION_TESTAPPTAFL AS TestApptAFL

SELECT TestAppt.Subject FROM UNION_TESTAPPT AS TestAppt UNION ALL SELECT TestApptAFL.Subject FROM UNION_TESTAPPTAFL AS TestApptAFL

Any help would be much appreciated,
Cheers,
Pete

aware_support

The query appears correct. Why are you saying you had no luck with it?

pbrad

Hi,

I get the following error:

Business object TESTAPPT not found in business space version

Any ideas?

Pete

aware_support

I didn't realize you were trying Aware IM query in the SQL form. This will not work across business spaces. You have to run this query outside of Aware IM or in a plug-in.

Generally speaking, if there is a requirement to have a query across business spaces you should merge two business spaces in one - Aware IM's concept of a business space implies data isolation between business spaces with services being the only way of communicating between business spaces.

pbrad

Okay thanks, I will figure out another way to manage it.

Cheers,
Pete