I have two data extension (DE1 and DE2) with a boolean field called Subscribe, an email address field called EmailAddress and a text field called Campaign. I want to create a Query with the following functionality:
Imagine an scenario like this:
DE1:
EmailAddress - Subscribe - Campaign
a@example.com - True - C1
a@example.com - False - C2
b@example.com - False - C1
b@example.com - False - C2
What I want to do is, in DE1 for each EmailAddress and for each campaign the information within Subscribe field is FALSE change the status field in DE2 from True to False.
DE2:
EmailAddress - Status
a@example.com - True
b@example.com - FALSE
In order to know if all the fields are false, I've created a query like this:
SELECT
ccall."Email Address",
'Unsubscribed' as Status,
ccall.Domain,
ccall.BounceCount,
ccall."Subscriber Key",
ccall.SubscriberType,
ccall.SubscriberID
FROM ENT.DE1 ccall //This is a Shared Data Extension
JOIN ENT.DE2 ALLS ON //This is a Shared Data Extension
ccall."Email Address" = ALLS.email
WHERE (ALLS.subscribe AS int)
SUM (ALLS.subscribe)=0
GROUP BY ALLS.subscribe
but the following error appear:
An expression of non-boolean type specified in a context where a condition is expected, near 'SUM'.
I don't know if maybe the problem is that I've written wrong the conversion from Boolean to Number or maybe there is something else wrong.