on 02-09-2009 4:22 PM
Hello,
I'm new at desiging a universe. I get the following a fatal error when trying to add a derived table to my universe:
1 processor x Family 15 Model 0 Stepping 0
Windows 2000 Workstation v5.1 build 2600 (S)
Physical Total/Avail: 2.097.151 Kb / 2.004.940 Kb
Temp directory C:\DOCUME1\xxxxxx\LOCALS1\Temp\ (1 Kb available)
'Unknown' video card
1280 x 768 in true color
(doesn't make sense to me)
I use an ODBC connection that connects to a MySQL database. When I test the connection it's fine.
I am trying to add a derived table because it seems that my universe cannot produce LEFT OUTER JOINS. I read somewhere that I should set my ansi92 parameter to YES instead of NO. But, everytime I re-open my universe, it sets itself automatically to NO again.
My utlimate goal is :
For example, list ALL the people, left join my table to their websites, but return ALL the people, even if they don't have a website.
I dont' understand, my join should be simple enough.
Anybody knows what could be causing all these restrictions ?
Thanks for you help.
Edited by: Sose Canadian on Feb 9, 2009 9:40 PM
Hi Sose,
In BusinessObjects (Universe) Designer, the outer join option in a join is not available and is dimmed when using a Generic ODBC connection.
Try Following Solutions:
Solution : 1
When using an ODBC connection the odbc.prm must be modified on the machine where BusinessObjects Designer is used.
Use the following steps to modify odbc.prm:
1. Locate odbc.prm in the following folder:
\Business Objects\Business Objects Enterprise 11\win32_x86\dataAccess\Connection Server\odbc
2. Open odbc.prm in a text editor and modify the parameter 'EXT_JOIN' so its value is 'YES' instead of 'NO'.The outer join option is now available in BusinessObjects Designer.
If the outer join option is still not available after following the steps above. open odbc.prm in a text editor and modify the parameter OUTERJOINS_GENERATION. Change its value from 'NO' to either 'ANSI92' or 'ODBC'.
Solution : 2
This can occur when user is using a non-database specific middleware such as generic ODBC. Generic ODBC allows creating a successful connection and universe, however will not allow the Outer Join option.
The syntax for outer joins is specific to each database and the Generic ODBC driver only lets you access standard common features of all ODBC data sources.
Ensure that you are using the database specific middleware to create the connection.
I Hope this Helpsu2026.
Thanksu2026.
Pratik
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sose,
When you make any changes to universe, You have to Export the universe for the changes to take effect. Make changes in ANSI 92 parameter then Export the Universe then Re-Import it you can see the Changes.
You can directly set Left outer Join but to use Full Outer Join functionality you have to Replace Universe Parameter ANSI 92 to u201CYesu201D
Letu2019s consider you are taking Left Outer join like this
Customer Left Outer Join Website
This will display all records from the Customer table even if donu2019t have records in the website table.
You have to take common field in both tables like ID which contain unique data to compare tables for data and join table based on ID and not on Customer name and website.
Please Update the Thread with the Following:
u2022 Which Fields you are using to join?
u2022 Cardinality you are using to join these tables?
I Hope this is clear....
Thanksu2026
Pratik
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
Thanks for the reply.
When I replace ansi92 to "Yes", I can now edit the "advanced" join, however, the system still disables the "Outer join" checkboxes beneath the tables I'd like to join. Seems to be the main problem to me.
Here are the fields I'd like to join:
People.PeopleID and People_website.PeopleID
Ideally, I'd like, for this join in my universe to return:
Select *
from People
left join People_website
on People.PeopleID = People_website.PeopleID
The cardinality is 1 to n.
Each website has one and only one people.
Each people has one or more websites
I exported my universe and it does not return all the people even if they don't have websites.
Thanks for your help.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.