SAP Knowledge Base Article - Public

1244979 - Is there an option to force Crystal report to use native Oracle SQL?

Symptom

  • Crystal Report generates SQL sentences as ANSI SQL
  • This causes some queries to crash

Reproducing the Issue

Environment information:

  • Crystal reports XI
  • Oracle DB 9.2

 

Cause

Since Crystal Reports XI, the SQL generated complies with ANSI SQL/99 standard for Oracle versions 9 and 10.

This is by design.

Resolution

Modify the SQL generated by using the Add Command functionality:

  • Go to Database > Show SQL Query
  • Copy the SQL generated
  • Go to Database > Database Expert
  • Locate your Data Source and open the database folder
  • Select Add command (top of the list) and add to selected tables by clicking on the > arrow. This opens the Add Command To Report window
  • Paste the SQL generated;
  • Modify the SQL as required and click OK
  • To rename the table, select and press F2
  • Go to the Links tab and check the relationships
  • Create the new report using the new table

Keywords

SQL generated, ANSI, Oracle, Crystal , KBA , BI-OD-CR , CrystalReports.com , How To

Product

SAP Crystal Reports XI