cancel
Showing results for 
Search instead for 
Did you mean: 

How to enable full join in my universe

Former Member
0 Kudos

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

http://www.forumtopics.com/busobj/viewtopic.php?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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

I suspect an error in the prm file: "FULL_EXT_JOIN" is set to NO, which signals to BO that the database does not support full outer joins.

See the Data Access Guide for more information.

Former Member
0 Kudos

Hi Emily,

Thanks for replying. Actually, I was able to enable the full outer join by adding ANSI 92 = Yes in IDT for both the business and data foundation layers.

Answers (0)