Use variable in the WHERE clause of Salesforce Connector v13.1

[#1]

Hi all:

We have an incremental reload script which only update the records with last modified date after the last reload.

The following script works for Salesforce Connector Version 11

[Table Name]:

SQL SELECT

     Id,

     [Some other fields]

FROM [SomeTable]

WHERE LastModifiedDate >=$(vLastExecStartTime) and LastModifiedDate < $(vExecStartTime)

;

We are updating the connector to 13.1 due to TLS and we found that the the original script does not work in the new connector.

We realised that 'SQL SELECT' has changed to 'SELECT'. But the script only works when we remove the WHERE clause.

Are there any different between the way to write the WHERE clause? Is it because of the variable?

Thank you very much.

PC

[#2 · Accepted solution]

Hi all:

We finally make it works.

It turns out that there must be a space between the operator and the variable in the WHERE clause.

So the following will not work:

WHERE LastModifiedDate >=$(vLastExecStartTime) and LastModifiedDate < $(vExecStartTime)

But this will work:

WHERE LastModifiedDate >= $(vLastExecStartTime) and LastModifiedDate < $(vExecStartTime)

We also found that the space between field name and operator can be ignore.

The error message 'Custom read failed' is not very helpful in debugging.

Thank you.

Paco

[Anonymous]

Hi Paco,

Have you encounter an error when fetching data from CaseHistory from Salesforce?  I get an error with the SQL 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';

Do you have a solution in case you encounter it?

Thanks,

Stewart