nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

Find more posts tagged with

Accepted answers

All comments

user2053645

That's not what I'm looking to do. Just an observation about the tool compared to some other BI tools I've seen. I do need to use obiee for the purposes

of presenting information. I don't have another option presently. Thank you.

Gianni Ceresa

Did you open a SR and asked Oracle?

I guess I saw some threads where that kind of cases ended up being fixed with MOS giving some "magic keyword" to set in the analysis as variables to influence the way OBIEE generate queries. (I didn't read all the details in the Word files, just looked quickly)

Martin van Donselaar

I have never had or seen issues with this no, I suggest that you follow Gianni's advise and raise an SR

user2053645

Gianni - Thank you. Yes, I did open an SR with Oracle. Here is some more information that might be of interest. Any thoughts?

I was able to create an outer join where there was only 1 physical column join between two tables.

In my example, I have 3 columns that are part of the physical join between the two tables.

Is that the issue? Is there a way to do an outer join where there are multiple columns that are part of the physical join?

Thomas Dodds

Why don't you have a single surrogate key that takes into account the data source, integration and other key values? Simplify ...

user2053645

Thomas - Are you talking about in the database or in obiee? If in the database, yes, completely agree.

Our modeling team is looking at that option. But if you're talking about doing something in obiee, please elaborate. I attached

a screenshot (word doc) to the ticket with how I set up the 3 key join (I just spelled it out in the expression area).

Thanks.

Srinivas Malyala-Oracle

option a: You can create views with all the tables your looking for in physical layer and use it as table.In view you can specify any type of sql (any joins).

another possible option: Create a view with surrogate key(concatenating 3 columns with some separator character) for each table in the Physical Layer and use this as join.

option b: If data source is always coming from one source you can ignore this datasource column in join. If integration_Id is unique then you may not needed additional check on INTRVN_NUM so you can ignore this column too. But I am not how your data is organized. Take a look at you may not need additional 2 conditions.

Thomas Dodds

I'm referring to the database ... use drives physical data model design.