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

Using Snowflake I am searching for entries where an individual carried out an action. The way I have to search means that I have to search 2 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 query like 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 time.

    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 may also be going about this the wrong way, I really would prefer to be able to set up 24 different peoples 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 simply write their name and have it execute properly, calling on the pre-stored data to formulate the proper clause but my technical knowledge is not at the standard where I can do that. I've tried defining a list of individuals and ID's in a CTE, I've tried DECLARE statements, sub queries and temporary tables but my knowledge does not allow me to understand/troubleshoot the errors I get.

As a last resort I figured I could just put everyone in using parenthesis and AND/OR in the WHERE condition but testing with simply 2 people breaks the BETWEEN function somehow.

CodePudding user response：

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

CodePudding user response：

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)