I'm running into this strange issue without being able to understand why it is happening. I want to run this simple query:
select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
where a.[Account:Id] not in (select s.Account__c from SurveyTaker__c_Salesforce as s)
the result is 0 rows. The problem is that, if I run the exact same query with the IN function:
select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
where a.[Account:Id] in (select s.Account__c from SurveyTaker__c_Salesforce as s)
the result is still 0 rows. If I remove the WHERE condition, I can find more or less 36k rows as a result. If I run the nested query separately:
select s.Account__c from SurveyTaker__c_Salesforce as s
the result is more or less 150k rows.
I cannot explain why this is happening and I would like to ask you if there is a possible explanation for this issue since NOT IN function is used in many queries on my Business Unit and if something changed I will have to rework them all.
Of course, if I write the query this way it works fine:
select s.Account__c
from SurveyTaker__c_Salesforce as s
inner join [Salesforce_Data_Event_DE] as a on a.[Account:Id] = s.[Account__c]
and the result is about 1.4k rows.
Do you know if something changed about the NOT IN and IN functions? Or can you notice a mistake I've made on the query?
Thank you, Francesco
I will try to explain myself better:
select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
this query result is 36k rows. Now I want to exclude from these 36k records all those who are inside another DE (SurveyTaker__c_Salesforce), so I run the following query:
select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
where a.[Account:Id] not in (select s.Account__c from SurveyTaker__c_Salesforce as s)
the result is now 0 rows. So we can think that everyone in the 36k is in the DE called SurveyTaker__c_Salesforce. At this point, I would expect that, if I run the query with the IN statement the result will be of 36k since everyone is into this DE:
select a.[Account:PersonContact:Id]
from [Salesforce_Data_Event_DE] as a
join Account_Salesforce_3 as b on a.[Account:Id] = b.[Id]
where a.[Account:Id] in (select s.Account__c from SurveyTaker__c_Salesforce as s)
also this query gives as a result 0 rows.
Thus the IN/NOT IN statement is not working properly or I've made a mistake I still cannot find!
Account_Salesforce_3
so it is possible there are records that match betweenSalesforce_Data_Event_DE
andSurveyTaker__c_Salesforce
but they do not exist onAccount_Salesforce_3
- which would cause 0 results returned. Have you checked to see results returned if you remove the In/Not In statement?Salesforce_Data_Event_DE
exists onAccount_Salesforce_3
since it is a subset of the Account. I've checked to see results returned if I remove the IN/NOT IN statement and the query returns 36k rows. With both the IN and the NOT IN statements it returns 0 rows and it has no sense at all