BI Publisher (MOSC)

MOSC Banner

Is it possible to do an Outer join on datasets?

in BI Publisher (MOSC) 3 commentsAnswered ✓

I have 2 databases that I have to pull & join data from.

I need to get SalesAgentNumber, StartDate, SalesAmount for the year into a report.


SalesAgentNumber & StartDate are both in database 'DB1'.

SalesAgentNumber & SalesAmount are both in database 'DB2'


So in BiP, I have created 2 datasets:


G_1

----

Select SalesAgentNumber, StartDate from DB1.Dim_SalesAgent



G_2

----

Select SalesAgentNumber, sum(SalesAmount) from DB2.Fact_Sales where SalesDate >= 1/1/2020



So for my datamodel, I then join SalesAgentNumber from G_1 to SalesAgentNumber in G_2.


Dataset G_1 will return 10 rows (for 10 different Sales Agents).

Dataset G_2 will only return 8 rows, since 2 Sales Agents did not have any sales in 2020. 

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center