Outer Joins Generated in SQL but not specified in Data Foundation

[Former Member]

Hi:

I created a very simple universe on a MS Access database. there are four tables, joined by equi joins. Yet, when I select the objects (one from each table), the generated SQL has outer joins! This was not the case in XI 3.1 using Designer. Has anyone else run into this?

I tried to create a similar scenario with SQL server and I got the same issue.

Here is the generated SQL from the MS Access based query:

SELECT

Country.country,

Region.region,

City.city,

Customer.first_name,

Customer.last_name

FROM

Country,

Region,

City,

Customer,

{ oj Country LEFT OUTER JOIN Region ON Country.country_id=Region.country_id },

{ oj Region LEFT OUTER JOIN City ON Region.region_id=City.region_id },

{ oj City LEFT OUTER JOIN Customer ON City.city_id=Customer.city_id }

Notice all the outer joins -- they are not specified as outer joins in Data Foundation yet Webi Rich Client continues to generate the outer joins.

Thanks!

-Mike

[Henry_Banks]

Hi,

This sounds familiar.

I think this may have been a bug recently solved in Patch2.10 for BI 4.0 SP02

Whenever a user creates a join in the data foundation using drag & drop (and not editing the join using the UI), the flag for defining the outer join status is improperly set.

This causes the SQL generation to create a left outer join when the UI displays that no outer join has been set.

The workaround is to edit the join (double-click to show the join editor) and then do OK. In this case a inner join will be used in the SQL generation.

This was confirmed fixed (in an internal discussion) in Patch2.10 . Does it sound familar ?

Regards,

H