1

I have a simple query where I want to see how many records were not added in DE. But with below query I am getting error "Errors: An expression of non-boolean type specified in a context where a condition is expected"

select 
q.email_address, q.uuid
from usca_restaurant_contact_v3 q
where (q.email_address, q.uuid) not in 
(select p.email_address, p.uuid from usca_restaurant_contact_v3_chkng_pk p)

Any other way to do this? I am trying to see how many records from usca_restaurant_contact_v3 were not added into usca_restaurant_contact_v3_chkng_pk?

2 Answers 2

1

Just from a quick glance, I would use a left join instead for this. See below:

select 
q.email_address, q.uuid
from usca_restaurant_contact_v3 q
left join (
    select p.email_address, p.uuid
    from usca_restaurant_contact_v3_chkng_pk p
) b
ON q.email_address = b.email_address AND q.uuid = b.uuid
WHERE b.email_address IS NULL
AND b.uuid IS NULL

By using the above WHERE statement it will only pull records from q that do not exist in p.

9
  • I tried but getting an error.. Errors: Incorrect syntax near the keyword 'WHERE'
    – Sohail
    Commented Sep 5, 2019 at 21:07
  • sorry forgot the ON statement, will edit now Commented Sep 6, 2019 at 12:52
  • didn't work. Showing 0 record.
    – Sohail
    Commented Sep 6, 2019 at 14:18
  • Have you verified that there are records where both email and uuid do not exist in q but do exist in p? If one exists, but not the other, it will not be displayed. For instance in q you have a record with [email protected] | 909 and in p you have [email protected] | 909 - these would not show up in your target as 909 is shared, so it is not null for both. Commented Sep 6, 2019 at 15:15
  • They do exist in Q but not in P. And that's what I want to check that how many records from P did not load into Q.
    – Sohail
    Commented Sep 6, 2019 at 18:46
1

The solution provided by Gortonington should work.

Alternatively, please try

SELECT 
q.email_address, q.uuid
FROM usca_restaurant_contact_v3 q
LEFT JOIN usca_restaurant_contact_v3_chkng_pk p
ON q.email_address = p.email_address AND q.uuid = p.uuid
WHERE p.email_address IS NULL
AND p.uuid IS NULL

You may also want to double check:

  • if column types are the same between the email_address and uuid columns in the 2 data extensions
  • if accidently any leading/trailing spaces were added during upload

To debug, you may also want to add an additional AND statement to your WHERE clause comparing for a concrete record you expect.

PS: Note there are also solutions out there like DESelect that allow you to build and easily test this kind of selections without having to write SQL queries.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .