1

the data is 1:1 with SF on the sql server with same named columns, yet...

SELECT ID,TIER__C  FROM [salesforce_stage].[dbo].[SFDC_ACCOUNT] where  TIER__C NOT IN ('C1', 'E1', 'E2', 'E3', 'E4', 'WHOLESALE')

gives me 850 rows of results vs:

SELECT ID, TIER__C FROM ACCOUNT WHERE TIER__C NOT IN ('C1', 'E1', 'E2', 'E3', 'E4', 'WHOLESALE')

309361 results on salesforce.

I'm not sure why there is such a huge discrepancy. Is there something related to soql syntax with IN that is drastically different from sql server?

  • 1
    Handling of null TIER__C values perhaps? Think SOQL will return the null value rows whereas there is this option ion SQL Server docs.microsoft.com/en-us/sql/t-sql/statements/…. – Keith C May 17 '17 at 20:52
  • Quick test of that: add and TIER__C != null to the SOQL. – Keith C May 17 '17 at 21:01
  • I assume you are using something like DBAmp as a linked server on SQL Server. If so, are you running the SOQL query as the same API user? There could be access restrictions on the user in question. – Daniel Ballinger May 18 '17 at 0:06
  • The other likely problem is around paging the results. You may find that 850 is the number of records being returned in the first query page and that it isn't then iterating the remaining pages. I'd try two things: 1) Use the OPENQUERY syntax in SQL to use the SOQL directly. 2) Try using something like Workbench with the configured API user to run the SOQL query. – Daniel Ballinger May 18 '17 at 0:08
  • hmmm seems like null is an issue. time for more investigation. thanks keith – J. Zhang May 26 '17 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.