Salesforce Connector 13.x and 14.x error when fetching CaseHistory table

[Anonymous]

Hi,

I get an error when fetching data from CaseHistory table from Salesforce.  I test both Salesforce Connector 13.x and 14.x.  The error and SQL is below.

Unable to get column information for the fields that are used in the query. Please make sure you have only one SELECT statement in a query editor. Description: Relationship queries with polymorphic key (keywords TYPEOF, Owner, Who and What) are not supported by the connector.

SQL SELECT

Id,

CaseId,

NewValue,

OldValue,

IsDeleted,

CreatedDate,

Field

FROM CaseHistory

where CreatedDate >= 2009-01-01T00:00:00.000Z and CreatedDate <= 2009-02-01T00:00:00.000Z  and Field ='Owner';

Any idea on how to resolve the error?

[johnca]

I use version 11 connector and don't have any issues using your SOQL. I set the API to 38. You may also want to try it without the SQL designation a la;

SELECT

Id,

CaseId,

NewValue,

OldValue,

IsDeleted,

CreatedDate,

Field

FROM CaseHistory

where CreatedDate >= 2009-01-01T00:00:00.000Z and CreatedDate <= 2009-02-01T00:00:00.000Z

and Field ='Owner';

I haven't much success using the newer connectors.

[Anonymous]

Hi John,

Version 11 connector is working fine.  However, we need to migrate to the new connector 13 or 14 because our Salesforce environment is deprecating TLS 1.0.  Version 13 and 14 support Salesforce TLS 1.1 and 1.2. 

Have you use version 13 or 14 and the sql works for you?

Thanks,

Stewart

[johnca]

Hi Stewart,

I tried version 12 a couple years ago and it was just too inconsistent. I will have to try v13 or v14 and see how it goes. The problem (for now) is switching back and forth between versions...uninstall/install...a pain. I will look into whether or not our environment will be affected, but fortunately I have another computer I can try this on but we're in the middle of upgrading to QlikView version 12.

V/r,

John

[johnca]

So, I was able to use connector version 13.1 and get the query to run in both Bulk and SOAP connectors, but I had to utilize a preceding load for the Field = 'Owner' clause. Is this acceptable to you? I don't use SOAP version but included it just the same. I also changed the date ranges for my data.

Using Bulk...

BULK:

Load *

     Where Field = 'Owner';

Select

Id,

CaseId,

NewValue,

OldValue,

IsDeleted,

CreatedDate,

Field

FROM CaseHistory

where CreatedDate >= 2017-04-01T00:00:00.000Z

And CreatedDate <= 2017-04-26T00:00:00.000Z;

And using SOAP...

SOAP:

Load *

     Where CaseHistory.Field = 'Owner';

Select

Id,

CaseId,

NewValue,

OldValue,

IsDeleted,

CreatedDate,

Field

FROM CaseHistory

where CreatedDate >= 2017-04-01T00:00:00.000Z

And CreatedDate <= 2017-04-26T00:00:00.000Z;

HTH,

John

[Anonymous]

I did end up using the preceding load with version 13.1.  However, the result pulling by Bulk or SOAP is not consistent. There are missing data.  I am not sure if it's because the CaseHistory contains big dataset.

[johnca]

It wouldn't due to a large dataset, that would return some other error I have not yet encountered. I made multiple requests using the same SOQL (but without the "SQL" preceding the Select) and got consistent results. I used a date range as you did but I didn't go back to 2009...I stayed in 2017. What I think might be happening is records may be being deleted(?) and those are no longer available. Query All is supposed to return all records regardless if they've been deleted or not.

I found out that we indeed are deprecating TLS 1.0 soon and need to upgrade to 1.1 or higher. <sigh> . I've already  found a few instances where my code needs to be updated. Maybe we'll get there...