How to effectively "outer join" two data sources (SQL queries) in OBIEE 11g data model?

0
votes

I have two SQL queries in a data model, each for a different data source. I am trying to link one to another to the effect that creating a report with all columns will not exclude values not matching in each of the queries. Simply linking elements seems to "inner join" when creating a report (excludes values which do not match in both queries). I am looking to essentially "left join" one source with another in report creation - I WANT null values to display from the other source.

When I review the data in the data model itself (via the data tab) null values are showing. It is only in the report creation that I am having this problem.

I am not a DBA and have read-only database access. I do not have access to any OBIEE desktop tools.

Example

3
sqldatabaseoracle11gobiee
It would help to see these queries and maybe some sample data. – Tim Biegeleisen
Added Example link to demonstrate the problem and to show what is needed. Assume basic "select * from" queries for simplicity. – user10194781

3 Answers

0
votes

Figured it out. Trick is to add a "dummy row" via UNION to the second table and create a "dummy column" in the first table (via CASE WHEN column IS NULL) that uses some placeholder value in place of null values. Then, link both tables using the dummy column. That way the null values are not excluded in the actual column when the two queries are linked. When creating the report, just exclude the dummy column.

0
votes

Use a simple left join between query1 and query2:

select q1.name,q2.name from  query1 q1 left join query2 q2
on q1.num=q2.num2
order by q1.name

http://sqlfiddle.com/#!4/ef642/3

NAME    NAME2
ExA1    ExB1
ExA2    
ExA3    
ExA4    
ExA5    ExB4