Hi together,

I'm having a problem concerning cross-database joins and I would like to ask for some help since I can't get the two sources together.

Here's a view from a CRM system (db=MS SQL Server 2005)

ACX
SEGNAME=WF_PARTNER,
TABLENAME=CRM.DBO.WF_PARTNER,
CONNECTION=crm_prod, KEYS=0, $

MAS
FILENAME=WF_PARTNER, SUFFIX=SQLMSS, $
SEGMENT=WF_PARTNER, SEGTYPE=S0, $
FIELDNAME=SET, ALIAS=SET, USAGE=A1, ACTUAL=A1, FIELDTYPE=R, $
MISSING=ON, $
FIELDNAME=PARTNER, ALIAS=PARTNER, USAGE=A15, ACTUAL=A15, FIELDTYPE=R,
MISSING=ON, $

I would like to join additional information from a view in another system (db=Oracle 10g) to this masterfile:

ACX
SEGNAME=WF_LAST_ORDER, TABLENAME=ORA_OWNER.WF_LAST_ORDER,
CONNECTION=ps_prod, KEYS=0, $

MAS
FILENAME=WF_LAST_ORDER, SUFFIX=SQLORA, $
SEGMENT=WF_LAST_ORDER, SEGTYPE=S0, $
FIELDNAME=PARTNER, ALIAS=PARTNER, USAGE=A15, ACTUAL=A15,
MISSING=ON, $
FIELDNAME=ORD_VALUE_SUM, ALIAS=ORD_VALUE_SUM, USAGE=D20.2, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=ORD_END_MAX, ALIAS=ORD_END_MAX, USAGE=HYYMDS, ACTUAL=HYYMDS,
MISSING=ON, $


So in the end I would like to have a masterfile with both synonyms and data matching view a left_outer_join over the field PARTNER.

I have done a lot of reading in manuals and the search concerning these topic, but I don't get this together (this is a simplified example). I'm able to get these two sources together via a match command at runtime, but not via a join in the masterfile.
I won't post my tries at this point, because I have tried many different ways. Some did work, but in the next case they won't although I'm doing it the same way.

Unfortunately some clicks in the synonym editor does not solve the problem yet. Can anyone construct the access and masterfile combination that should work for a cross-reference-join (oracle/ms sql server) including all valuable attributes or explain THE way to join these sources?

Thanks for any suggestion!

Kind regards,
JP

This message has been edited. Last edited by: Kerry,

JP, what you are trying to do is definitely NOT a best practice even if you could get it to work. You are essentially doing a cross-platform, hetergeneous join which really isn't supported. WebFOCUS winds up doing the join anyway.

The best way to accomplish this join is to take control and to bring back the required data from the two sources separately, make the target of the join a temp FOCUS file, and then do the join yourself.

hmm, I found these statement in a presentation:

quote:

Metadata is the layer of technology that shields the user from data complexities and presents the information in business terms.

It describes the relationship between items whether or not they reside in the same DB or not.

WebFOCUS metadata can even describe cross platform cross databases relationships. No other tool can do this.

So, these statement tells me that it is possible to have a cross plattform/cross database relationship. But there is a retriction that these different plattforms should reside on the same database system (like System A on Oracle and System B also on Oracle)? It is a fact that a relationship between two differenz databases is not supported? In a masterfile?

According to that the best (or the only supported?) way to achieve this is to create a FOCUS database of both views (which can become quite big) and join these two sources in a secound step. Afterwards I can create one permanent masterfile (with titles, descriptions, OLAP, etc.) for temporary content in the joined FOCUS database? These full functionality of a masterfile with all its attributes is a very important point for our customer.

JP

I'm not saying you can't do it. I'm saying it is not a good idea. Also you don't have to create FOCUS data bases out of either of the hold files as long as they are sorted in the same order.

Even if you use the metdata layer method, the data will STILL be brought back to WebFOCUS for joining. If you can get it to work and it performs, then great. I just don't recommend it.

Even though the documentation says its possible, and it is, there isn't a magic process that connects the two different databases together, apart from WF.

One of the major problems with this sort of join, either in a Master File or in code, is that there is a chance that all the records from each table may come back.

If the number of records is always small, then I guess you can get away with it.

Ginny's suggestion is the safest option, TABLE each source then join.

For performance reasons, I would avoid getting WebFOCUS processing involved until the final answer set has been generated. I would use pass-thru SQL to perform the cross-database JOIN, and any filtering, summation, and sorting. SQL cross-database join