Problems with INNER JOINS under Oracle 9i database

[Former Member]

Hello all,

My problem is related about the generation of the SQL query for a Oracle 9i database from Crystal Reports 2008. The generation of reports gets the data through ODBC connected to Oracle 9i database, which supports the execution of SQL querys that use the INNER JOIN command for one SELECT query on different tables at the same time. The trouble comes when the query contains too much INNER JOINs. Crystal returns the error: Failed to retrive data from the database. Details: [Database Vendor Code: 1445] .I have test the same query rewritten in the WHERE form (the JOIN is represented in the WHERE clause) and works right. How Can I change some option in Crystal reports 2008 to generate the SQL query througt WHERE clause instead of INNER JOIN?

[former_member11696]

Hi Jose,

Minimum support for Oraccle with CR 2008 is 9.2 noted int he [Platforms |https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/e09198a1-911e-2b10-179f-ce8eed51aed0]info file.

You may want to test using other ODBC drivers or using OLE dB. ODBC does have a limit to the number of inner joins it can write.

Other option is to use a Command Table and paste in your query that way. CR then just sends that SQL directly to the server with no conversion. There is another registry option but so far I ahve not been able to make it work.

What you should be able to do is create this key:

HKEY_CURRENT_USERSoftwareBusiness ObjectsSuite 12.0Crystal ReportsDatabaseJoinBuilder

It's a string value and enter the name of the ODBC driver dll you are using. you can get the name from the DSN driver list when you created the DSN.

Thank you

Don

[Former Member]

Hi Don,

The option of to use a Command Table to specify the query that we want execute in database was successful, also with this method I optimize the SQL querys used in my reports.

Thanks a lot!