Snowflake: Using multiple AND/OR statements breaks my date filter

Question

Using Snowflake I am searching for entries where an individual carried out an action. I search for two identifiers AGENT_NAME and AGENTID and then I use a BETWEEN to search for actions created on that day. If I search for one person the report works perfectly. If I include a second person, the date column breaks and starts displaying all days.

When I run the this it works perfectly.

WHERE
    AGENT_NAME = 'John Wick'
    AND AGENT_ID = '1234'
    AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' AND '2023-01-17 23:59:59.000'

When I try to incorporate multiple people like this, the ACTION_CREATED_DATE column displays results from all times.

WHERE
    (AGENT_NAME = 'John Wick' AND AGENT_ID = '1234')
    OR (AGENT_NAME = 'Tom Cruise' AND AGENT_ID = '5678')
    AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' AND '2023-01-17 23:59:59.000'

I would prefer to set up 24 different people's AGENT_NAME and AGENT_ID somewhere else within the same query as I do not have permissions to create separate tables/schemas etc, and then in the WHERE statement write their name, calling on the pre-stored data. I tried defining a list of individuals and ID's in a CTE, DECLARE statements, subqueries and temporary tables.

I put everyone in using parentheses and AND/OR in the WHERE condition but testing with two people breaks the BETWEEN function.

[SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/q/1241142/3404097) — philipxy, Jan 20 '23 at 06:16
Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help]. Reflect research in posts. — philipxy, Jan 20 '23 at 06:17
Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad definition/query/function with the obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & ideally a [mre]. But please ask about bad code 1st because misconceptions get in the way of your goal. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Jan 20 '23 at 06:24

score 0 · Accepted Answer · answered Jan 19 '23 at 19:32

0

It requires additional parenthesis around OR:

WHERE
    ((AGENT_NAME = 'John Wick' AND AGENT_ID = '1234')
    OR (AGENT_NAME = 'Tom Cruise' AND AGENT_ID = '5678'))
    AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' 
                                AND '2023-01-17 23:59:59.000

A more readable way is using IN operator:

WHERE
 (AGENT_NAME,AGENT_ID) IN (( 'John Wick', '1234'), ('Tom Cruise','5678'))
 AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' 
                             AND '2023-01-17 23:59:59.000

answered Jan 19 '23 at 19:32

Lukasz Szozda

162,964
23
234
275

Thank you, such a simple fix. If I was to end up with a final query that had 24 different OR statements, does it require even more parentheses? – ZKRC Jan 19 '23 at 19:36
@ZKRC If you have more OR blocks you need one parenthesis around all of them i,e. "((A AND B) OR (B AND C) OR (D AND E))" – Lukasz Szozda Jan 19 '23 at 19:37

score 0 · Answer 2 · answered Jan 19 '23 at 19:34

You need to put brackets around the things that are in each OR and the OR as whole.

WHERE A OR ( b AND c) OR (d AND e) AND f AND g

most likely should be

WHERE (A OR ( b AND c) OR (d AND e)) AND f AND g

but OR are bad for performance as a big picture, so another way is to run many passes and UNION ALL the distinct parts together,

thus:

WHERE A AND f AND g 

UNION ALL

WHERE (NOT A) AND ( b AND c) AND f AND g 

UNION ALL

WHERE (NOT (A AND b AND c)) AND f AND g 

...

thus give you each leg of the OR tree as distinct SETS of values, will perform much faster (if you can write it this way, albeit it might be super more ugly to read, and prove is equal/correct)

Snowflake: Using multiple AND/OR statements breaks my date filter

2 Answers2