Automation Studio Query not accepting parentheses in combined AND OR

Question

We've run into a problem which prevents us from combining ANDs and ORs in the WHERE clause of a SQL statement. This is the syntax of our WHERE statement:

SELECT 
s.subscriberKey
FROM 
entryZomerIntent s
WHERE 
s.subscriberKey IN (
    SELECT email FROM [ACM - eventTrigger] 
    WHERE step LIKE '%Step 3%' 
    AND booked <> 'True'
)
AND 
(
s.subscriberKey NOT IN (SELECT subscriberKey FROM [ExclusionList1]) 
OR 
s.subscriberKey NOT IN (SELECT subscriberKey FROM [ExclusionList2]) 
)

We want check if the subscriber isn't in either of the two ExclusionList DE's we made. However, when I check the syntax of this query it gives the following error:

An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax

When I remove the parentheses around the OR statement the error dissappears, but now the conditions don't get processed properly. Without the parentheses Marketing Cloud treats the conditions before the OR as one part and the one condition after the OR as the other part of the condition instead of only running the OR between the two ExclusionList subqueries.

How do I make this WHERE statement execute properly? I'm certain the parentheses syntax should be correct but for some reason Marketing Cloud isn't having any of it.

Answer 1

The query you are trying to run should be accepted by query validation. It looks like there is a potential bug in validating nested select statements which precede AND/OR groupings.

Placing the nested select at the bottom of your query will allow it to validate.

SELECT 
s.subscriberKey
FROM 
entryZomerIntent s
WHERE 
(
s.subscriberKey NOT IN (SELECT subscriberKey FROM [ExclusionList1]) 
OR 
s.subscriberKey NOT IN (SELECT subscriberKey FROM [ExclusionList2]) 
)
AND s.subscriberKey IN (
    SELECT email FROM [ACM - eventTrigger] 
    WHERE step LIKE '%Step 3%' 
    AND booked <> 'True'
)