Outer Join in Universe

[Former Member]

Hi,

We are on XI3.1.

I added an outer join on tableB and set both of the universe parameters OUTERJOINS_GENERATION and ANSI92 to yes. But I am not getting what I expect. The following are the example of the records on the tables.

TableA

20090701 123 A

20090702 456 B

20090703 444 C

20090704 333 D

20090705 222 A

TableB

20090703 test1 test2

20090705 test1 test2

Result I would like to see:

20090701

20090702

20090703 test1 test2

20090704

20090705 test1 test2

Result I am getting with the setup mentioned above

20090703 test1 test2

20090705 test1 test2

Could you please let me know if the universe can support what I need. If yes, how I can set the universe up?

Thanks.

Fan

[Former Member · Accepted solution]

Hi,

What is the back end database you are using. If it is Db2 like that then you have to some config settings. When I was working in BOXI R2 I also faced same problem. You try to go to C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer in that check the PRM file for your database and check whether outer join is enabled or disabled for you.

Please ignore this if not useful.

Cheers,

Suresh Aluri.

[Former Member]

Fan,

I added an outer join on tableB

What steps did you perform for this?

Can you generate the SQL and post it here?

Thanks,

John

[Former Member]

Hi John,

Thanks for getting back to me.

I tried a couple other things since the post.

I added another outer join to Tbl3 which is a security view. I think the security view might be preventing the outer join from working. The outer joins looks like below:

Tbl1 = tbl2

tbl2 = tbl3(+)

The following is the sql when OUTERJOINS_GENERATION = 'Yes' is not added to the universe parameter list.

SELECT

ENTERED_DATE.DATE_DIM_ID,

max(DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.CLIENT_NATIONAL_ACCOUNT),

sum(DATA_WAREHOUSE2.VALID_REGISTRATION_DAILY.UPC_COUNT)

FROM

DATA_WAREHOUSE2.DATE_DIM ENTERED_DATE,

DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW,

DATA_WAREHOUSE2.VALID_REGISTRATION_DAILY

WHERE

( DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.USERID=@variable('BOUSER') )

AND ( DATA_WAREHOUSE2.VALID_REGISTRATION_DAILY.CLIENT_DIM_ID=DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.CLIENT_DIM_ID(+) )

AND ( ENTERED_DATE.DATE_DIM_ID=DATA_WAREHOUSE2.VALID_REGISTRATION_DAILY.ENTERED_DATE_DIM_ID(+) )

AND (

ENTERED_DATE.DATE_DIM_ID >= 20090701

AND DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.CLIENT_DIM_ID = 12

)

GROUP BY

ENTERED_DATE.DATE_DIM_ID,

DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.CLIENT_DIM_ID

The following is the sql when the OUTERJOINS_GENERATION = 'Yes' and ANSI92 = 'Yes' are set in the universe parameter.

SELECT

ENTERED_DATE.DATE_DIM_ID,

max(DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.CLIENT_NATIONAL_ACCOUNT),

sum(DATA_WAREHOUSE2.VALID_REGISTRATION_DAILY.UPC_COUNT)

FROM

DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW RIGHT OUTER JOIN DATA_WAREHOUSE2.VALID_REGISTRATION_DAILY ON (DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.USERID=@variable('BOUSER') AND DATA_WAREHOUSE2.VALID_REGISTRATION_DAILY.CLIENT_DIM_ID=DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.CLIENT_DIM_ID)

RIGHT OUTER JOIN DATA_WAREHOUSE2.DATE_DIM ENTERED_DATE ON (ENTERED_DATE.DATE_DIM_ID=DATA_WAREHOUSE2.VALID_REGISTRATION_DAILY.ENTERED_DATE_DIM_ID)

WHERE

(

ENTERED_DATE.DATE_DIM_ID >= 20090701

AND DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.CLIENT_DIM_ID = 12

)

GROUP BY

ENTERED_DATE.DATE_DIM_ID,

DATA_WAREHOUSE.CLIENT_DIM_SECURED_VW.CLIENT_DIM_ID

Neither of the sqls gives me what I would like to have. I can not remove the security view since it will return everything on tbl3 which is not what I want. I wonder if the universe designer supports union function.

Thanks.

Fan