SAP Knowledge Base Article - Public

2930165 - Database error when adding a SQL Query in a Command Object in Crystal Reports

Symptom

  • Database Error.
  • Unable to add a SQL Query to a report.
  • When adding a custom SQL Query in a Command Object in Crystal Reports, it fails with a database error. 

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020 

Reproducing the Issue

  1. In Crystal Reports, create a new report, and connect to a data source.
  2. In the "Database Expert", double-click on "Add Command". 
  3. In "Add Command To Report", enter a SQL Query.
  4. When clicking on the button: "OK", it displays a database error.

Cause

  • Crystal Reports sends the SQL Query entered in the Command Object, to the database. If the SQL Query is invalid, the database server will generate an error.   
            
  • Also, Crystal Reports is a reporting tool, and open a read only connection to the database, therefore it only support SQL Query Language in a Command Object that retreives data from a data source like:
    - SELECT
    - FROM
    - WHERE
    - GROUP BY
    - ORDER BY
    - UNION
      
    And Crystal Reports does not support SQL Query Language for data manipulation like:
    - CREATE TABLE
    - ALTER TABLE
    - INSERT
    - INTO
    - UPDATE
    - DELETE
    - DROP TABLE
    - ALTER SESSION
    - TEMP Table
    - Variable Declaration
    - Etc...
       
    If the SQL Query contains any data manipulation SQL, it will fail, because Crystal Reports is a reporting tool, and not a data manipulation tool.  
      
  • For reference, see the Crystal Reports User Guide section on: How To Create a Command Object

Resolution

  • Command Object is a powerful feature that enables a report developer to enter a SQL Query manually to retrieve data, but with power, comes responsibilities, therefore when using a Command Object to enter a SQL Query, it is the report developer's responsibility to ensure the SQL Query syntax is correct. If you need assistance in writing the SQL Query, contact your database administrator, or consult your database documentation.
       
  • Once the SQL Query syntax can be executed successfully directly on the database, and it does not contain any Data Manipulation SQL, then you will be able to add the SQL Query in a Command Object in Crystal Reports.

Keywords

CR, Command Table , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020