Can you filter on a Boolean/Checkbox in a Marketing Cloud SQL Query

Question

I have a Synchonised Data Extension for SFSC Contact in SFMC. There is a Custom Checkbox field in the Contact object that I am trying to use as a filter in a SFMC Query. The SFMC field was automatically created as a boolean when I created the Synchronised DE.

The query fails without an error of I manually execute it and if I add it to an Automation Studio automation it errors but there is a generic message "System failure" or sumsuch.

Query:

SELECT
c.Id
, c.Email
, c.FirstName
, c.LastName
, c.Checkbox__c
FROM Contact_Salesforce c
WHERE c.Checkbox__c = 1

If I change the filter criteria to something like c.FirstName = 'Jim' the query works OK so everything else seems to be set up propery.

Have also tried WHERE NULLIF(c.Checkbox__c,0) = 1 to no avail.

If I create a filterd DE with the same criteria I get records as expected.

I could create another custom formula field in SFSC with a string 'TRUE' or 'FALSE' and filter on that but that just seems silly.

Does anyone have any idea or thoughts?

Cheers

Answer 1

Try subbing True for 1 and 0 for False. It should work. In SFMC these work as booleans values.

SELECT 
c.Id 
, c.Email 
, c.FirstName 
, c.LastName 
, c.Checkbox__c 
FROM Contact_Salesforce c 
WHERE c.Checkbox__c = "True"