2

I have 3 data extensions: Master, Local and additional, supporting one which essentially functions as historical Storage of all the records that ever landed in Local. So the data flows like this Master > Local > Storage

Now, I want to have a query which will transfer data from Master to Local but only if such record doesn't already exist in Record Storage. I am using overwrite function as neither Local nor Storage have a Primary Key.

To determine whether a record exists in Storage I check two different fields and only if such combination doesn't already exist in Storage, I move the record to Local.

Everything works as intended until the moment when in Storage appears a record where one of these two fields is empty. From that point on the query below doesn't work anymore and always brings 0 records from Master to Local. Any idea how to get rid of the problem?

SELECT 
    *
FROM
    [Master]
WHERE
([Task:Description] +''+ [Task:What:Case:Id]) NOT IN (SELECT ([Task:Description] +''+ [Task:What:Case:Id]) FROM [Storage])    
  • check out this link stackoverflow.com/questions/5231712/sql-not-in-not-working. A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values. – Eduard Sep 8 '17 at 10:13
  • 1
    yup, adding IS NOT NULL seem to solve the problem. This is the updated query: SELECT * FROM [Master] WHERE ([Task:Description] +''+ [Task:What:Case:Id]) NOT IN (SELECT ([Task:Description] +''+ [Task:What:Case:Id]) FROM [Storage] WHERE [Task:Description] IS NOT NULL ) – Piotr Caputa Sep 8 '17 at 12:55
0

This is rather T-SQL question. A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values. Add IS NOT NULL in the sub-query Where clause. This will filter out all rows whith NULLs and, hence, the IN statement will work as you expect.

Check out this link .

|improve this answer|||||
  • Please summarize what you found instead of just linking to another post. – Adam Spriggs Sep 14 '17 at 11:20
  • @AdamSpriggs thank you for your review. Actually that was my point A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values. I put a link because I found the answer there. I will update the post anyway. – Eduard Sep 14 '17 at 16:33

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.