How to enable full join in my universe

system · November 28, 2014, 10:20pm

Hi All,

I have created a Microsoft SQL Server 2008 based universe in IDT that uses only 2 tables. However, when i try to retrieve a few fields from these 2 tables in Business Objects it returns the results as an inner join. When i tried to change the join conditions in the data foundation layer to left or right outer join by checking the boxes in front of the tables (where it only allows me to check one box but not both at the same time), it still does not work as i really need to have a full join between the 2 tables.

I came across this post on BOB and SCN respectively that mentions about how to enable ANSI 92 parameter in the data properties but i am still unable to check both the boxes in IDT that will allow me to get a full outer join even after doing an ANSI 92 = Yes in IDT

https://bobj-board.org/t/29838
http://scn.sap.com/thread/3238413

Also attached are 2 snips from the data foundation layer of my universe. Here is how my odbc.prm files look like:

<?xml version="1.0" encoding="UTF-8"?><DBParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../dbparameters.xsd"> 
<Configuration> 
<Parameter Name="DB_TYPE">GENERIC</Parameter> 
<Parameter Name="ORDER_BY_REQUIRES_SELECT">YES</Parameter> 
<Parameter Name="GROUP_BY_SUPPORTS_COLUMN_INDEX">NO</Parameter> 
<Parameter Name="JOIN">YES</Parameter> 
<Parameter Name="INNER_JOIN">INNER JOIN</Parameter> 
<Parameter Name="EXT_JOIN">NO</Parameter> 
<Parameter Name="UNION"></Parameter> 
<Parameter Name="UNION_IN_SUBQUERY">NO</Parameter> 
<Parameter Name="INTERSECT"></Parameter> 
<Parameter Name="INTERSECT_IN_SUBQUERY">NO</Parameter> 
<Parameter Name="MINUS"></Parameter> 
<Parameter Name="MINUS_IN_SUBQUERY">NO</Parameter> 
<Parameter Name="DISTINCT">NO</Parameter> 
<Parameter Name="CONSTANT_SAMPLING_SUPPORTED">NO</Parameter> 
<Parameter Name="ANALYTIC_CLAUSE"></Parameter> 
<Parameter Name="PERCENT_RANK_SUPPORTED">NO</Parameter> 
<Parameter Name="RANK_SUPPORTED">NO</Parameter> 
<Parameter Name="ANALYTIC_FUNCTIONS"></Parameter> 
<Parameter Name="SEED_SAMPLING_SUPPORTED">NO</Parameter> 
<Parameter Name="FULL_EXT_JOIN">NO</Parameter> 
<Parameter Name="LEFT_EXT_JOIN">NO</Parameter> 
<Parameter Name="LEFT_OUTER"></Parameter> 
<Parameter Name="RIGHT_EXT_JOIN">NO</Parameter> 
<Parameter Name="RIGHT_OUTER"></Parameter> 
<Parameter Name="GROUP_BY_SUPPORTS_COMPLEX">NO</Parameter> 
<Parameter Name="GROUP_BY_SUPPORTS_CONSTANT">NO</Parameter> 
<Parameter Name="GROUP_BY">YES</Parameter> 
<Parameter Name="HAVING">YES</Parameter> 
<Parameter Name="ORDER_BY">YES</Parameter> 
<Parameter Name="ORDER_BY_SUPPORTS_COLUMN_INDEX">YES</Parameter> 
<Parameter Name="INTERSECT_ALL">NO</Parameter> 
<Parameter Name="MINUS_ALL">NO</Parameter> 
<Parameter Name="UNION_ALL">NO</Parameter> 
<Parameter Name="LIKE_SUPPORTS_ESCAPE_CLAUSE">NO</Parameter> 
<Parameter Name="SELECT_SUPPORTS_NULL">NO</Parameter> 
<Parameter Name="SUBQUERY_IN_FROM">NO</Parameter> 
<Parameter Name="SUBQUERY_IN_IN">NO</Parameter> 
<Parameter Name="SUBQUERY_IN_WHERE">NO</Parameter> 
<Parameter Name="CALCULATION_FUNCTION">YES</Parameter> 
</Configuration>

I found this file at the following location:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc

[Moderator Edit: Added code formatting - Andreas]

nashpatrick (BOB member since 2014-11-28)

system · December 1, 2014, 11:17pm

i’m not sure if there’s a way to get IDT to do the full outer join. You could always put the syntax in a derived table and use the derived table to build your objects on.

erik.stenson (BOB member since 2012-07-30)

system · May 20, 2016, 7:06pm

I was just searching for the same thing - how to implement a FULL OUTER join in IDT for SQL Server. Found the answer on SCN here. Basically, you have to set ANSI92 = Yes in both the DF and BLX (and change the .PRM file).

charlie (BOB member since 2002-08-20)