- Error: "Failed to retrieve data from the database. Details: 42S22:[Simba][SQLEngine[(31400) Qualified column could not be bound: <Object Name> [ Database Vendor Code: 31400 ]"
- When refreshing a report in Crystal Reports 2013, based on Salesforce.com, it fails with the error.
- Same report refresh successfully in Crystal Reports 2011 and below.
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
Reproducing the Issue
- In Crystal Reports 2011 or below, create a new report on Salesforce.com data source.
- Add a Command Object, and enter a SOQL query that uses alias like:
Select c.Account.Name, c.id, c.name from contact c
- Notice the report refresh successfully.
- Refresh the same report in Crystal Reports 2013, and it will fail with the error:
"Failed to retrieve data from the database.
Details: 42S22:[Simba][SQLEngine[(31400) Qualified column could
not be bound: c.Account.Name [ Database Vendor Code: 31400 ]"
- The issue occurs due to the table alias used in the SOQL, and alias are unfortunately not supported by the Salesforce.com ODBC driver bundled with Crystal Reports 2013.
- A product enhancement was created to request the support of Alias under SAP Note 2124918
- Crystal Reports 2011 and below did not use the Salesforce.com ODBC driver. It was connecting using a JDBC driver. And this is why it is working in Crystal Reports 2011 as the JDBC driver bundled did not have this limitation.
- The Salesforce.com ODBC driver in Crystal Reports 2013 was enhanced to support Salesforce.com Alias in a Salesforce Query (SOQL) in the following product update:
- Crystal Reports 2013
- Support Pack 07 and above
- If you cannot apply the latest product update that contains the enhancement, then to successfully refresh the report in Crystal Reports 2013, remove the Alias from the SOQL query.
- Open the report in Crystal Reports, and under the menu "Database", select "Database Expert..."
- In the "Database Expert" window, under "Selected Tables", right click on the command, and select "Edit Command"
- In the "Modify Command" window, remove the Alias used in the SOQL, and click "OK"
For example, if the report was using the following SOQL where the Contact table was alias with "c"
Select c.Account.Name, c.id From Contact c
Replace the alias, "c", by the actual table name, which is "Contact". In this example, the SOQL will then look like:
Select Contact.Account.Name, Contact.id From Contact
Sforce, SOQL, CR , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem
SAP Crystal Reports 2013 ; SAP Crystal Reports 2016