• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5892
  • Last Modified:

BO Universe Outer Join Problem

BOE XI-R2 SP-2

I have a big universe that's built off a Sql 2005 database that is set to Sql 2000 compatibility.  ANSI-92 compatibility for this universe is set to "No".

I wanted to use CLR integration on this database the other day, so I set its compatibility to Sql 2005.  But that broke many reports due to the fact that there are several joins in this universe that use the old-style outer join operators "*=" and "=*".  So I had to set it back to Sql 2000 compatibility.

I've tried setting ANSI-92 to "Yes", which simply removes the "*" from these outer-join operators, making them inner joins (not what I need).  But when I try to replace the old-style operators with LEFT OUTER JOIN or whatever, the BOE universe designer doesn't recognize those.  Parsing the join fails.

I'm thinking--I'm HOPING--this is because I did not have all 3 mojos going at the same time:  db set to 2005 PLUS the universe set to ANSI-92 PLUS the join operator being LEFT OUTER JOIN instead of *=.  I only had the universe set to ANSI-92 with the LEFT OUTER JOIN syntax.  (I had set the DB back to 2005-compatibility because I was killing some production reports).

Can anyone confirm this?  Will it work when I have all 3 of these planets lined up?  Or am I screwed somehow, being stuck at XI-R2 SP-2 indefinitely?

Thanks!
0
bamapie
Asked:
bamapie
  • 7
  • 7
1 Solution
 
MIKESoftware Solutions ConsultantCommented:
copy the UNIVERSE to a test area...and TRY IT...????

Or create new Universe with Configuration you are needing..???
0
 
MIKESoftware Solutions ConsultantCommented:
I shy away, especially with BIG UNIVERSES from making any kind of GLOBAL CHANGES LIKE THIS....
0
 
bamapieAuthor Commented:
I've copied and tried it.  Had to copy the DB, too, since this isn't a DB-only change.

Can't get the expression right--or can't get the expression where it's palatable to BO.

Old join expression syntax is:

RSSQL1.dbo.vwShiftRunLineDowntimeTotals.RunShiftKey=*vwDailyProductionReportShiftRunData.RunShiftKey

BOE-UniverseDesign-ChangingNonAN.jpg
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MIKESoftware Solutions ConsultantCommented:
Recreate a NEW Universe using the correct compatibility for SQL 2005......
0
 
bamapieAuthor Commented:
I'm not going to re-create this universe.

I have copied it and changed the compatibility.
0
 
MIKESoftware Solutions ConsultantCommented:
Does it WORK>...?????
0
 
bamapieAuthor Commented:
Did you read my post of 29 Oct?
0
 
MIKESoftware Solutions ConsultantCommented:
Does this WORK / Help...?


".....self-joins in the universe are generated in the FROM clause rather than in the WHERE clause. For reports that don't involve the self-join, the sql may be different but returns the same results. Here is the bug information I got from BO tech support:

ADAPT00754142

Description:

Self-join conditions that are used in query may fail or may reduce performance.

The cause of the problem is that self-join conditions were generated within the WHERE clause of the query by default.



New Behavior:

This problem is resolved.

To solve the problem, previous changes made in response to another issue (ADAPT641525) have been reversed: instead of

generating self-joins in the WHERE clause, by default self-joins will be generated in the FROM clause. However, users have

the option to generate self-joins in the WHERE clause by using this new parameter: "SELFJOINS_IN_WHERE=yes.


(This parameter must be addd to the universe parameter tabs on any universe with self-joins).
0
 
bamapieAuthor Commented:
I've read that.  That's BO basically saying they've fixed something.  I don't see evidence of whatever they're describing having fixed my issue.

I'm going to also include a snapshot of the error seen when I attempt to run a query against the BO universe with the ANSI setting change PLUS the sql db with 2005 compatibility ON.
BOE-UniverseDesign-ChangingNonAN.jpg
0
 
bamapieAuthor Commented:
It had been stale since my last post Oct 29.  My posting the delete request has caused it to fire up.
0
 
mlmccCommented:
Is the new information helpful?

Please remember all experts are vounteers and work when they have time.  Also there aren't many BO Universe experts so so,etimes a response takes time.

mlmcc
Zone advisor
0
 
bamapieAuthor Commented:
I found what it was.  This DB had a couple of non-ANSI views (joins using the *= and =* operators).

I rewrote the queries of these views, and all is well.

I'm awarding points because the notion of copying the universe and DB was a safe route to take...
0
 
MIKESoftware Solutions ConsultantCommented:
Hum....I thought we identified those "*=" and "=*" join operators early on ....???

Oh well, glad it all worked out.

M
0
 
bamapieAuthor Commented:
These weren't joins in the universe.  They were embedded inside the scripts of the views themselves.  Which were then joined (normally, non-outer) to other entities in the universe.
0
 
MIKESoftware Solutions ConsultantCommented:
Ahhh I see...I see....ok..good.

THANKS for the added info.
M
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now