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:
EmailAddress - Subscribe - Campaign
firstname.lastname@example.org - True - C1
email@example.com - False - C2
firstname.lastname@example.org - False - C1
email@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.
EmailAddress - Status
firstname.lastname@example.org - True
email@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.