VS2008 SSRS using Oracle query: how to stop {oj} for external connections?

Question

I am using VS2008 SSRS to query and report Oracle Database. Whenever I create a query designer to add an outer join, it adds {oj} for example.

OJ Added

SELECT A.*,B.* FROM {oj A LEFT OUTER JOIN B On A.id = B.id} 

OJ Not Added (As I Want It)

 SELECT A.*,B.* FROM A LEFT OUTER JOIN B On A.id = B.id 

The only way I can run the query is "Edit as text ..." and delete {oj ...}

Is there a way to stop Visual Studio 2008 from adding {oj} when I want an external connection, without the need for "Edit as text"

Answer 1

I assume that you have yet to find a solution. I am sure that the Oracle driver for SSRS is simply incorrect - there are no problems with SQL Server data sources. I can’t believe that more people don’t complain about it.

It worked, but used the old (+) style for external joins up to a year or so ago. I assume that at this point there was some update that tried to change it to a new syntax, but it exploded by including the curly braces oj, which seems to be the normal syntax for some other sources, but it certainly does not work with Oracle .

Yes, of course, you can edit it in the text query designer, but if you switch to the graphic designer, it will immediately return curly braces, etc. I like having a graphical setting and it really should work!

The problem has been with our suppliers since it first appeared, but they seem unable to understand the problem. They occasionally email me to find out if there is a problem! They probably hope for a miracle.

Answer 2

MSDN Says that the {oj} syntax should work in Visual Studio. However, he also says that the (+) syntax also works. So you can try -

 SELECT A.*,B.* FROM A , B WHERE A.id = B.id(+); 

While the query described below will work in Visual Studio, it will not work only with any Oracle DB Client tools (Toad, SQL Developer, SQL prompt, etc.).

 SELECT A.*,B.* FROM {oj A LEFT OUTER JOIN B On A.id = B.id}; 

Answer 3

One of our guys finally discovered that if you go into text editing mode, you can cut out {oj and then save the request. (In our case, a named query) Until you return to the visual mode in the report designer, it will save the edited query and works fine. However, if you switch to visual mode, {oj returns again.