Skip to Content
0

CR 2013 unable to use a variable in a where clause in salesforce - Is there an update to fix this?

Nov 04, 2016 at 03:19 PM

46

avatar image
Former Member

We are having issues with Crystal Reports using a SalesForce database. The machine I am using has CR 2013 with Support Pack 7 (ver 14.1.7.1853)

The error message I receive is: Failed to retrieve data from the database. Details 42S22:[Simba][SQLEngine] (31400) Qualified column could not be bound: Owner.State. [Database Vendor Code: 31400 ]

There is an existing support ticket resulting in the same error which claims that having CR 2013 with Support Pack 7 SHOULD fix the issue. However this ticket references table aliases which do not seem to be the issue here. For reference, here is that ticket: https://apps.support.sap.com/sap/support/knowledge/public/en/0002118506

The workaround in the above ticket is to rewrite the queries in the reports to not use aliases. However in our case, it's not an "alias" generating the error, it's actually a defined relationship between the tables. The error generated is the same one, but it's possible that that it's a completely different cause.

Our particular query is based on custom tables most SalesForce databases do not have, however I can replicate the issue using a very basic query linking the Account and User tables using the Owner fieldrelationship. The error occurs only when the "relationship" is used to filter in the Where clause along with a Crystal Reports formula (does not occur when Owner.State is merely included in the Select clause). The query below, if used in a "Command" for a Crystal Report, will generate the error. However if I replace the parameter {?State} with an actual state abbreviation such as 'NH', the report functions correctly and without errors.

SELECT Id, Name, AccountNumber, Owner.State
FROM Account
Where Owner.State = {?State}

I'm attaching a version of the report where {?State} has been replaced with 'NH' (otherwise I can't save it). If you update this report to use a parameter in the Where clause (against a SalesForce database), it will give the error I described above.

Getting this issue resolved is of the utmost important to get us off of Crystal Reports 8 and onto Crystal Reports 2013. Many of our reports involve Command queries with filtering like this in the Where clause and using parameters (otherwise the data pulls are too large and time out).

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Vitaly Izmaylov
Nov 07, 2016 at 05:53 PM
0

Have you tried to add quotes around the Parameter?

like:

SELECT Id, Name, AccountNumber, Owner.State
FROM Account
Where Owner.State = '{?State}'

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 21, 2016 at 04:39 PM
0

Hi,

Thank you for your response. This works for a string field but when I pass a date it fails again. Is there a sp or upgrade that fixes this issue
? Or can I not pass dates a variable? Thanks in advance.

Josh

Share
10 |10000 characters needed characters left characters exceeded