We recently upgraded to 4.2 SP6 and are struggling with some new joins in an existing universe. We usually create outer joins between tables and we will end up with a left outer join, but after the upgrade, it seems no matter what we do the join always turns into a right outer join. This is causing our queries to not return. Can someone help with some suggestions on why suddenly it is always creating right outer joins and not left outer joins like before? It says it joins tables according to their weights from largest to smallest, but its doing just the opposite of that. It starts with the smallest table and then goes up to the largest table. I already checked and we don’t have the parameter set that causes this functionality.
Not a fix, but perhaps a work around. Create a view with the correct join rules, then you can apply your filters in the report to that. It’s an ugly thing, but may keep things running while the real answer bubbles to the top. Perhaps only for a few key reports.
Although if the VP report is not done, the VP can help apply pressure to SAP to fix things.
Have you set the outer join options on each join in the UDT? Or are you relying on table weights, which won’t, as you’ve seen, give the correct outcome.
TableA left outer join TableB on TableA.ID = TableB.ID
should give you the same results as
TableB right outer join TableA on TableB.ID = TableA.ID
which is what it should be doing. If it’s using a right outer but not changing the table order, that’s a problem, but I haven’t run into that. What’s your data source? You might also want to take a look at the ANSI92 settings in the universe parameters and, if it’s set to No, in the PRM file.