0

I am trying to run below query, which successfully run but it does not retrieving any data. I am 100% confident it should retrieve the data as I have manually checked.

What could be the reasons its not working, and any solution?

SELECT * FROM REMARKETING D
WHERE D.PNumber NOT IN
(
SELECT P.QNumber FROM POLICY P
)
  • Qnumber could be null? It could be the issue – m Peixoto Dec 4 '18 at 16:15
  • IF Qnumber is null then it should retrieve all the data from Remarketing? right? that's what I want. So currently I can see 92 records in Remarketing DE but after running SQL, the DE is showing 0 record. – Sohail Dec 4 '18 at 16:20
  • But it is a not IN. Make it in consideration.... If it is a IN, what you are saying would make sense. – m Peixoto Dec 4 '18 at 16:25
  • If I will say IN then it will only bring in the matching records. If I will say NOT IN then it will bring in all the non matching records. If I understand correct. And I want to bring in non matching records. So I want to retrieve only Pnumbers those are not found in Qnumber of Policy DE. – Sohail Dec 4 '18 at 16:30
  • If you test null against anything, it will return true. So, if there is QNumber equal null, your first query will return nothing. – m Peixoto Dec 4 '18 at 16:33
1

Try this instead:

SELECT D.fielda, D.fieldb, D.fieldc 
FROM REMARKETING D
LEFT JOIN POLICY P ON D.pnumber = P.pnumber
WHERE P.pnumber is NULL

You'll have to adapt the join to account for what field you're matching on. You're where statement will give you all the records that are not matched by the join.

| improve this answer | |
  • Except you'll need to SELECT explicit fields, so you can't use * – Matthew Wash Dec 4 '18 at 16:59
  • @MatthewWash That's true -- updating. – Tom Callahan Dec 4 '18 at 17:00
  • I can not use the join method. And the above query was working perfectly fine until 10/15. Suddenly after 10/15 query retrieve no records. – Sohail Dec 4 '18 at 18:12
  • Why doesn't the join method work for you? What type of Query activity are you using? Are you getting an error message within the Query activity log? – Tom Callahan Dec 4 '18 at 18:16
  • No i am not getting any error. The query runs successfully. If I will going to join then whole logic will no more work. In Remarketing table their is a Pnumber field in Policy table their are two fields Pnumber and Qnumber. In policy table when record status change for prospect then a new record created with new Pnumber and old Pnumber goes into Qnumber for the same row. – Sohail Dec 4 '18 at 20:45

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.