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

building reports crystal reports
1

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!

MJRBIM :canada: (BOB member since 2007-03-23)

2

Hmmm. Thats a bad one there, have you tried logging a support call as that sounds like a bug. Also, have you checked for multiple versions of Oracle client files (OCIW32.DLL) and also Crystal Oracle Native drivers?

Only simple solution I could think of would be to recreate the report off an Oracle view or possibly use a command object.

ABILtd :uk: (BOB member since 2006-02-08)