I had the experience that when you are linking data providers from different database platforms, the data type may not be exactly the same. In my case, it was Oracle and DB2. I had to use substr function to eliminate spaces from one column in one data provider so the linking works properly. Hope it helps.
Lucia
From: Rao, Vinay (GEP, Contractor)
[SMTP:Vinay.Rao%gepex.ge.com@internet.kraft.com] Sent: Wednesday, March 08, 2000 9:48 AM
I need to generate report based on two data providers.
First data provider details.
GRADE,COLOR,PID
Second data provider details
GRADE,COLOR,PID,Customernumber.
I need all the customer numbers from the second data provider if grade,color,pid of 2nd DP is there in 1st DP.
I am linking both data providers based on GRADE,COLOR,PID. I am getting all
the rows which are there in both data providers.
ex:
1st data provider
GRADE COLOR PID
1 R 1
2 R 1
3 R 1
2nd data provider
GRADE COLOR PID Customer Number
1 R 1 1000
2 R 1 2000
4 R 1 3000
result
GRADE COLOR PID customer number
1 R 1 1000
2 R 1 2000
3 R 1
4 R 1 3000
last two rows which I do not want to come in results.
Unfortunately, BO does a full outer join when linking data providers so that you will get all results from both queries. It would be nice to be able to select the way you want the link to occur.
I have had similar situations and handled it by doing either a subquery or using an exists clause in the query. For example, your first query would look like:
select grade, color, pid from [table name a] where exists (select * from [table name b] where a.grade = b.grade and a.color = b.color and a.pid = b.pid)
You would need to do something similar for the second query as well.
I hope this helps.
Marian Cooney
McKessonHBOC
Malvern, PA
I am linking both data providers based on GRADE,COLOR,PID. I am getting all the rows which are there in both data providers.
…
last two rows which I do not want to come in results.
In a message dated 00-03-08 10:55:37 EST, you write:
I need all the customer numbers from the second data provider if
grade,color,pid of 2nd DP is there in 1st DP.
I am linking both data providers based on GRADE,COLOR,PID. I am getting all
the rows which are there in both data providers.
Try using a filter on Customer Number, something like:
=Not(IsNull())
That will reduce the list to only those records that have a defined customer number. Unfortunately, this is the best solution when linking data providers. As mentioned by someone else, BusObj does a Full Outer Join on linked data providers. At the current time there is no way to configure the type of join.
If you build your first data provider and the click the combine queries button you will be able to use a subquery to link with the parent query. By using this method you will be able to utilise SET operators (UNION, INTERSECT, MINUS) which will provide the functionality that you require , for example
Query A INTERSECT Query B = Return ONLY those records that are in Query A AND Query B.
Query A MINUS Query B = Return ONLY those records that are in Query A AND NOT IN Query B.
Query A UNION Query B = Return ALL records returned BY Query A AND ALL records returned BY Query B.
In which case I believe the INTERSECT operator will provide the solution to your problem.