CR-9 to CR-2008 (Oracle Native SQL issue)

[MJRBIM]

Migrating a report from CR-9 to CR-2008 which uses the "Oracle Native" driver (Oracle 9i).

There are a large number of LINKED tables with LEFT-OUTER JOINS in the Query which runs fine in CR-9.

In CR-9, the SQL is executed with the LEFT-OUTER JOINS in the WHERE statement -

eg. WHERE "TABLE_A"."PROJECT_NUMBER"="TABLE_B."PROJECT_NUMBER" (+))....etc

In CR-2008, the SQL is executed with the LEFT-OUTER JOINS in the FROM statement -

eg. FROM ("ABC"."TABLE_A" "TABLE_A" LEFT OUTER JOIN "ABC"."TABLE_B" "TABLE_B" ON "TABLE_A"."PROJECT_NUMBER"="TABLE_B"."PROJECT_NUMBER")....etc

Because of the large number of LINKED tables with LEFT-OUTER JOINS in the Query, the FROM statement from CR-2008 is being cropped/truncated by the Oracle 9i driver, and the SQL request is FAILING.

Anyone know of a good way to fix this issue either on the RPT-side in CR-2008, or by changing the SQL LENGTH rule in Oracle...?

Thanks in advance for the help!



Senior Business Intelligence Consultant
Toronto, Canada

SeagateInfo 7 -> Crystal Enterprise -> BOE-XI (R2) / (R3.1) | Crystal ( 7 -> 2008) design | WebI design | Best practices for Java / .Net / Web Services

 

[Turkbear]

Hi, as a first step, copy the Sql Query created by CR2008 and run it in SqlPlus to see if it really is getting truncated by Oracle and not by CR ...

If it is too long then a view created in the database to do the joins would probably be the better approach.





To Paraphrase:"The Help you get is proportional to the Help you give.."