Qlik Community

QlikView Connectors

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Use variable in the WHERE clause of Salesforce Connector v13.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

1 Solution

Accepted Solutions
Not applicable

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

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

2 Replies
Not applicable

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

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

smac_panw
New Contributor

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

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