0

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.

  • 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 Jul 10 '17 at 9:14
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().

SELECT
  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
| improve this answer | |

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.