Linking Dataproviders

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.

Please suggest me some solution.


Listserv Archives (BOB member since 2002-06-25)

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.

Please suggest me some solution.

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (24 hrs. a day now!): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’


Listserv Archives (BOB member since 2002-06-25)

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.

Please suggest me some solution.


Listserv Archives (BOB member since 2002-06-25)

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.

Can you say, “Enhancement request” ?

Regards,
Dave Rathbun
Integra Solutions
www.islink.com


Listserv Archives (BOB member since 2002-06-25)

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.

Paul Jarrett
Gamar Limited
United Kingdom


Listserv Archives (BOB member since 2002-06-25)