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:
Now if I complete the mapping to look like this, I can generate code.
As you see by default (this is OWB 10.2.0.3) you get an ANSI join syntax:
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 (3)
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 | July 23, 2008 1:58 AM
Posted on July 23, 2008 01:58
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 | July 24, 2008 12:38 AM
Posted on July 24, 2008 00:38
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 | July 29, 2008 7:28 AM
Posted on July 29, 2008 07:28