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
[email protected] - True - C1
[email protected] - False - C2
[email protected] - False - C1
[email protected] - 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
[email protected] - True
[email protected] - FALSE

In order to know if all the fields are false, I've created a query like this:

    ccall."Email Address", 
    'Unsubscribed' as Status,
    ccall."Subscriber Key",
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.

  • the error message is fairly descriptive. The syntax of your WHERE clause is incorrect and I'm not sure I understand the semantics, either. SQL's expecting some sort of expression or comparison that results in a boolean to take place here, but there isn't one. Are you just wanting to update records in DE2 where DE1.Subscribe = 0 ("false")?
    – Macca
    Commented Jul 10, 2017 at 9:14

1 Answer 1


You'll need to use a HAVING clause for conditions involving aggregation.

And you can't use double-quotes for column names that contain spaces. Use brackets instead.

Also, you're missing a cast() in your GROUP BY and your SUM().

  ccall.[Email Address]
, 'Unsubscribed' as Status
, ccall.Domain
, ccall.BounceCount
, ccall.[Subscriber Key]
, ccall.SubscriberType
, ccall.SubscriberID
FROM ENT.DE1 ccall
INNER JOIN ENT.DE2 ALLS ON (ccall.[Email Address] = ALLS.email)
GROUP BY cast(ALLS.subscribe as int)
HAVING SUM(cast(ALLS.subscribe as int)) = 0

You must log in to answer this question.

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