What can cause an outer join not to work?
I was reading in the archives and someone mentioned that outer joins can be negated. When does that happen?
I have a report showing Orders and Invoices. I want it to return all order numbers in a particular date range whether or not an invoice has been generated against it. Is there another way to do this?
Thanks.
Susan Fowler
ISM Fastening Systems
Listserv Archives (BOB member since 2002-06-25)
Susan,
I think you can have two different contexts for Orders and Invoices . And ,including a join (may be outer one ) with Invoices, in the Orders context should generate 2 queries with their result set synchronised …(a 2 way outer join ) will show you all order numbers irrespective of whether their corresponding Invoices are generated or not and vice-versa .
Thanks,
Vikas
What can cause an outer join not to work?
I was reading in the archives and someone mentioned that outer joins can be negated. When does that happen?
I have a report showing Orders and Invoices. I want it to return all order numbers in a particular date range whether or not an invoice has been generated against it. Is there another way to do this?
Thanks.
Susan Fowler
ISM Fastening Systems
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-03-17 12:10:18 EST, you write:
I was reading in the archives and someone mentioned that outer joins can be
negated. When does that happen?
It depends on how your database processes outer joins, but a condition on the “outer” portion of the join will effectively negate the “outer” part of the join. For example, assume table A and table B are joined in an outer fashion, with table B being the child table (with possible missing rows). The following SQL will be okay:
select stuff
from A, B
where A.ID = B.ID(+)
The (+) is Oracle syntax. Now, if you try to also say that some field in B (say B.date1) has a condition, then you have a problem:
select stuff
from A, B
where A.ID = B.ID(+)
and B.date1 = sysdate
As soon as the SQL is parsed, the outer join becomes negated since the second condition on table B is not part of the outer expression. You would need something like:
select stuff
from A, B
where A.ID = B.ID(+)
and B.date1(+) = sysdate
Again, Oracle syntax. As luck would have it, there is a paper on our web site that details some ideas / tricks for working with outer joins. You can find it at
http://www.islink.com/bobjtech.htm
Look for the article on outer joins. It specifically mentions Oracle, but can be adapted for use with other databases.
I have a report showing Orders and Invoices. I want it to return all order numbers in a particular date range whether or not an invoice has been generated against it. Is there another way to do this?
As long as you don’t have a condition on invoice information, you should be fine. Your condition on Orders within a date range should be okay, since the Orders table is the driving table. It’s the Invoices table that may have missing data.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
Does any one know why i can’t do an Outer Join, between two tables ? In my Universe i have two tables, example: table 1: Accounts (with 1 : 0/1) table 2: telefones
I just have to do an outer join between Accounts and telefones, because I want all accounts even with no telefones! The result is the same with equal or outer Join, the records of Accounts wich have a telefone number!
I realy don’t know what could be the problem !!
Listserv Archives (BOB member since 2002-06-25)
Sandra,
Are you applying the outer join by checking the outer join box ? If your join is a complex join and it constitutes many fileds then just by checking then did you put a plus manually in the join for all fields (even for if there is some condition having a constant at the other end
(e.g. flag (+) = ‘Y’ ? Check the SQL generated by your report .
thanks,
Vikas
Listserv Archives (BOB member since 2002-06-25)
What database and version of BusinessObjects are you using? Have you compared the SQL for the report with an equal join and the report with an outer join?
George Baranowski
QuadraMed
Listserv Archives (BOB member since 2002-06-25)
I have a problem in outer join in universe. I have a universe “Purchase” which is designed on star schema. I have a fact table “Purchase” and a dim table “Material”.
Purchase Fact Table --------- PO Number
Listserv Archives (BOB member since 2002-06-25)
Dinesh.Thoniparambil@GECAPITAL.COM
The outer join option in designer is greyed out. Any ideas why?
Thanks,
Dinesh.
BO 4.1.3
DB2 V 5.0
Listserv Archives (BOB member since 2002-06-25)
If you use multi columns join it will be greyed out. However, you make a single column join and check the outer join box before select other columns.
Daniel Guo
Ford Motor Company
Listserv Archives (BOB member since 2002-06-25)
The outer join box can be greyed out when the generic Odbc data source is used.
This is due to a parameter file in business objects not being set up for the generic data source.
To enable it you need to edit the odbc10.prm file and also put the appropriate syntax in for the code generation.
Mark Taft Act financial Systems
Listserv Archives (BOB member since 2002-06-25)
Hello -
Read through the archives and tried to implement the suggestion found above and in paper found on integra solutions website.
I have applied an outer join between Provider and Enrollee table. I have added (+) to objects within Enrollee table as that is the table that may or may not return a result. When I parse the object I get an Oracle Error. “Outer join operator (+), not allowed in select list.”
Solutions???
Tammy 
Tammy 
(BOB member since 2002-11-12)
You should apply the Outer join (+) in your JOIN and NOT in your OBJECT definition.
Can you post the code that is giving you the problem?
Andreas 
(BOB member since 2002-06-20)
I can’t believe I got a response so quickly…thought I was the only one working today…
I have applied the outer(+) to the join which works fine as long as I do not include a condition on an object in the Enrollee table.
For example, I want all Providers (Provider Table) and the program that they are enrolled in (Enrollee Table). However, I want all Providers even if they are not currently enrolled in anything.
This works fine by itself, however, when I apply a condition such as an enrollment_year = 2002, the outer join is negated and I only get Providers who where enrolled in something in the year 2002.
The solution (according to the UserOuter.pdf found on Integra Solutions website - unless I’m reading it wrong) is to add an outer symbol (+) onto the objects in the ‘outer table’ (in my case Enrollee Table) so that the outer is still applied when a condition is added.
When I add the (+) to the object in Designer I get the Oracle error. My co-worker thought this was curious too.
Tammy (BOB member since 2002-11-12)
If you’re talking about the ‘User Selectable Outer Joins’ paper, I think I remember that I tried it and it no longer worked with the version of Oracle we were on at the time. I remember talking with Dave about it but I don’t remember whether it was on the listserv…I can’t seem to search it up.
Cindy Clayton 
(BOB member since 2002-06-11)
Even though it does not parse in BO Designer, does it work when you are building a data provider with this object in BO Reporter?
Which Oracle version are you using?
Andreas (BOB member since 2002-06-20)
No, I saved the universe and created a query against it and got the same response. I think Cindy is right…I think it might be a change to a later version of Oracle. We’re on 8.1.6
Tammy (BOB member since 2002-11-12)
I’ve seen the error as well, it’s the new oracle version (9)
They decided to promote the ANSI join syntax (LEFT OUTER etc) and discourage the old outer join syntax (+)
One the restrictions is:
The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
So NOT in the select list, thanks oracle, it’s in loads of my universes. 
marianne 
(BOB member since 2002-08-20)