Hi all

Today i encountered a very interesting issue in WebFOCUS. The logic of my report is to Match to data sources and provide a comparison of the values which are common in both data sets. My team have already com up with a solution with the data source provided by our client. One of our data set comes from a relational database and other comes from SAP adapter.

In the solution the relational DB data set has two tables which are 1:1 related but Table A has more records than Table B so i need to do a “Left Outer Join” to create a data set. Then we will use MATCH command with AND to get the intersection records in the two data sets which will then be for comparison. And our solution was working fine.

Today we tried to change the SAP synonym the new one is created from a similar environment used in developing the report solution. After changing the synonym the report starts acting weird. In the new SAP data set and relational DB data set i have nearly 1500 common records which needs to be displayed in the report but the report only shows 15 records.

I could troubleshoot the problem up to a extend, that too seemed weird again. The issue comes from the “Left Outer Join” now. If i disable the join and remove the related fields from the report from that join the report runs as expected. I tried to switch to the previous SAP synonym and the report worked fine.

I have no idea how is this happening. Since I only changed the SAP's synonym the problem came from the relational DB join.

I will be updating results of extended troubleshooting. Meanwhile if any has encountered similar scenarios please help.

Regards
Robin

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

If the only item that has changed is the new synonym, verify that the usage and actual hasn't changed in the MFD.

Hi guys this issue came with the "LEFT_OUTER JOIN" and DEFINE statement with left outer join.(Define is to do a data type conversion)

Here the "LEFT OUTER JOIN" works as a INNER JOIN.

If i remove the DEFINE its working properly.

Note:- One more thing the issue is not with the SAP data set.


Regards
Robin

When i went through the Trace i found a intresting point. The LEFT OUTER JOIN when its working get's me a Query of

 SELECT T1. ST ,T1. SHIP  FROM DB2TEST1 T1 
SELECT T2. LOGIN_ID  FROM DB2TEST2 T2 WHERE (T2. STR = ?) 

it is also showing
"(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED"

Do any one have any idea on this ???

You were getting that before you put the trace in. You just couldn't see it. If I understand you correctly, you are joining an SAP table to a relational table. That is considered a hetergeneous join and as such must be performed by WebFOCUS. The solution is to select the data separately from each table using the WHERE conditions appropriate for each table, HOLD the data with the target being a FOCUS file with an index. Then do your join on the intermediate files.

This technique will give you more control and it will be more efficient as a result.