Trying to create an outer join in obiee

user2053645

I have created the following SQL in another tool and I'm trying to replicate the outer join in obiee.  In OBIEE, I have specified an outer join in the BMM

layer between the two logical tables and I've modified the logical table source also indicating an outer join.  However, when I view the sql in obiee after the

report is run, I still see an equi-join.

Directly below is what I want.  This SQL generated in 5 minutes easily in another tool:

SELECT DISTINCT AL6.FULL_NM, AL3.MED_REC_NUM FROM
UD_UNIVERSAL.D_DATE_V AL2, UD_UNIVERSAL.D_PATIENT_V AL3,
UD_CLINICAL.F_PT_INTRVN_V AL4, UD_CLINICAL.F_PT_INTRVN_SVC_PROV_V AL5,
UD_UNIVERSAL.D_SVC_PROV_V AL6 WHERE ( AL5.INTRVN_INTEGRATION_ID (+)= AL4.INTEGRATION_ID  AND
AL5.INTRVN_DATASOURCE_NUM_ID (+)= AL4.ORIG_DATASOURCE_NUM_ID  AND
AL5.INTRVN_NUM (+)= AL4.INTRVN_NUM
AND AL4.PATIENT_KEY=AL3.D_PATIENT_KEY AND
AL4.INTRVN_STRT_DT_KEY=AL2.D_DATE_KEY AND
AL6.D_SVC_PROV_KEY=AL5.INTRVN_SVC_PROV_KEY)
AND (AL2.CAL_DT BETWEEN '01-01-2018 00:00:00' AND '02-01-2018 00:00:00')

What I see in obiee:

WITH

SAWITH0 AS (select distinct T465794.FULL_NM as c1,

     T449460.MED_REC_NUM as c2

from

     UD_UNIVERSAL.D_PATIENT_V T449460 /* ALS_D_PATIENT_V */ ,

     UD_CLINICAL.F_PT_INTRVN_V T465736 /* ALS_F_PT_INTRVN_V */ ,

     UD_CLINICAL.F_PT_INTRVN_SVC_PROV_V T465715 /* ALS_F_PT_INTRVN_SVC_PROV_V */ ,

     UD_UNIVERSAL.D_SVC_PROV_V T465794 /* ALS_D_INTRVN_SVC_PROV_V */ ,

     UD_UNIVERSAL.D_DATE_V T465591 /* ALS_D_INTRVN_STRT_DT_V */

where  ( T449460.D_PATIENT_KEY = T465736.PATIENT_KEY and T465591.D_DATE_KEY = T465736.INTRVN_STRT_DT_KEY and T465715.INTRVN_DATASOURCE_NUM_ID = T465736.ORIG_DATASOURCE_NUM_ID and T465715.INTRVN_INTEGRATION_ID = T465736.INTEGRATION_ID and T465715.INTRVN_NUM = T465736.INTRVN_NUM and T465715.INTRVN_SVC_PROV_KEY = T465794.D_SVC_PROV_KEY and  TRUNC(T465591.CAL_DT) between TO_DATE('2018-01-01' , 'YYYY-MM-DD') and TO_DATE('2018-01-02' , 'YYYY-MM-DD') ) )

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select distinct 0 as c1,

     D1.c1 as c2,

     D1.c2 as c3

from

     SAWITH0 D1

order by c2, c3 ) D1 where rownum <= 1000001

Message was edited by: user2053645

Joel

Can you provide some more information like OS, OBIEE version and probably some screenshots of how you've configured the Logical table and how you've set up the outer join?

user2053645

Joel - Thanks so much.  Our exact obiee version is 11.1.1.7.150120.  Attached are screenshots.  I really appreciate any feedback.  I have

screenshots in a Word doc.  I'll find where to attach to the ticket and do so.  Thanks.

user2053645

I attached a Word doc.  Thank you.

Martin van Donselaar

Did you check if outer join support is disabled for your Database? (It should be on by default)

user2053645

Martin - I can perform an outer join against the same database using another tool.  So yes, the database itself supports outer joins.

Is the picture you showed a setting in obiee or at the database level?  Our database is Oracle 11.  It does support outer joins now.

Gianni Ceresa

It's the DB features for your physical database in the OBIEE RPD.

user2053645

Here is a picture from the connection in the physical layer.  Outer joins enabled.

Martin van Donselaar

That's what i meant yes

user2053645

So has anyone else seen issues with generating the outer join syntax in obiee?  I attached a Word doc with steps I've taken that I would think would impact

the ability to generate the syntax.  Thanks in advance.

[Deleted User]

@user2053645

"The outer join syntax is very simple to write and execute in a product like SQL Developer or TOAD.  What you write is what you get.  OBIEE seems “over engineered” for the purposes of changing the join type."

Is it? Ok so create heterogeneous multi-source models over XML files, 3 different database types, a Hadoop cluster, some MSAS cubes and Essbase.

Sarcasm aside: if you want to write SQL then write SQL in another tool.