Menu
I am using VS2008 SSRS to query and report a Oracle Database. Whenever I the query Designer to add an outer join it adds {oj} e.g.
OJ Added
SELECT A.*,B.*
FROM {oj A LEFT OUTER JOIN B On A.id = B.id}
OJ Not added(How I want it)
SELECT A.*,B.*
FROM A LEFT OUTER JOIN B On A.id = B.id
The only way I can get the query to run is to "Edit as Text..." and remove the {oj...}
Is there any way of stopping Visual Studio 2008 from adding the {oj} when I want an outer join, without having to "Edit as Text"
340
In this example, the left outer join between CUSTOMER and DAILY_SALES can be converted to an inner join because the predicate C. CUST_NAME = 'SMITH' will remove any rows with null values in this column, making a left outer join semantically unnecessary.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.
I assume you have yet to find a solution. I am pretty sure the Oracle driver for SSRS is just plain wrong - there is no problem with SQL Server data sources. I can't believe there aren't more people complaining about it.
It used to work, but used the old style (+) syntax for outer joins, until a year or so ago. I guess at that point there was some update which attempted to change it to the newer syntax, but blew it by including the curly bracket oj, which seems to be the normal syntax for some other sources, but it certainly doesn't work with Oracle.
Yes, of course you can edit it in the text query designer, but if you switch to the graphical designer it immediately re-inserts the curly brackets etc. I like to have the graphical option and it really ought to work!
The issue has been with our suppliers since it first occurred, but they seem quite unable to grasp the problem. They occasionally email me to ask if the problem still exists! I guess they are hoping for a miracle.
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
