Full Outer Joins in OWB

One of the questions I noticed recently was on full outer joins. How do you do that in OWB. Well it is quite simple, but I guess not that obvious. So here is a quick post on how to do this.

Here we have a simple mapping, where I join on code. I want to get all records (yeah not a great business case I know) from both tables, so I know if there are records on either side without matches.

My join looks like this:

JoinClause.JPG

Now if I complete the mapping to look like this, I can generate code.

MappingComplete.JPG

As you see by default (this is OWB 10.2.0.3) you get an ANSI join syntax:

ANSICodeForFullOuter.JPG

Want something not ANSI, go to configuration on the mapping and uncheck the ANSI SQL Syntax code option. Note that if you are doing Multi Config, you can change this per deployment of the mapping...

Comments:

Hi, in my opinion the problem is not how to define outer joins, but to make them errorfree. If you use the joiner deep in your mapping, the owb produces the join in deep subselects. Then you often have the problem, that the statement is executed as a simple join on the database. By my experiences everybody should test outer joins generated by owb if the result set really have outer join elements (Null values). To define them in the join operator is very trivial and too easy to explain it in this blog - in my opinion. Regards, Detlef

Posted by Detlef on July 22, 2008 at 06:58 PM PDT #

Yep, multi-joins involving outer joins are only safe if you control the nesting of the SQL which is generated by OWB. If you want to control nesting of your mappings, starting in OWB 10.2.0.3 this can be done by the "federation" feature. Just define a non-deployable "nest" Mapping with an unbound View as target operator. This "nest" View should be deployed and then goes as a source operator into an "embracing" Mapping. A little bit of a workaround, but you can clearly control nesting of mapping logic this way. I have also applied this for analytical functions, when I had to do filtering based on analytical calculations - need to have nesting there too. I have heared some rumours though that in future OWB releases nested mappings as a feature are on the way :) regards Lutz

Posted by Lutz Bauer on July 23, 2008 at 05:38 PM PDT #

I definitely agree with the statement about the full outer join being buried within sub-selects depending on how far into the mapping the F.O.J. in implemented. This has also caused us some performance related issues. I like the idea presented about the 'nest' mapping. I wonder if this can partly be accomplished with pluggable mappings? -Greg

Posted by Greg Partenach on July 29, 2008 at 12:28 AM PDT #

When I want a left outer join, I use the (+) only on the INGRP1 side of the condition, but it still generates a RIGHT OUTER JOIN in the code. Can anyone help on that?

Posted by guest on September 14, 2011 at 10:55 PM PDT #

Oracle join syntax can be confusing, put the (+) on the INGRP2 part if you want left outer.... see Oracle doc below...

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2107296

Cheers
David

Posted by David on September 15, 2011 at 05:21 AM PDT #

Hi,

I am new in OWB and would like to know on the concept of Cubes and loading cubes using Lookup operator.

Can anyone please provide some guideline on this ?

Thanks in advance..

Nilava

Posted by guest on June 01, 2012 at 02:12 AM PDT #

Hi Nilava

Have you looked through the OBEs?
https://forums.oracle.com/forums/ann.jspa?annID=1667

There is a blog below on lookup;
https://blogs.oracle.com/warehousebuilder/entry/owb_11gr2_lookup_operator

Cheers
David

Posted by David on June 01, 2012 at 07:57 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« February 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
       
       
Today