CODEWITHSUNDEEP
sqlapache-drill
Shan
Shan

Reputation: 81

SQL WHERE clause runs with AND but not with OR

I have below SQL query that filters out records based on a date field in WHERE clause. I have two different date attributes.

SELECT * FROM MY_TABLE WHERE DATE1 >= '2002-01-01' OR DATE2 >= '2002-01-01';

This SQL gives a data read error. But when I replace the OR with AND it works fine.

How do I filter records to pick any record that has a either of dates greater than some arbitrary date?

EDIT1

I feel my questions was quite incomplete. I am using apache drill query engine. Plus the actual query is a join on two tables with the 2 date columns residing in the two different joined tables as below

   SELECT
        A.id AS my_id,
        B.is_active is_active,
        C.other_id AS id2
    FROM
        A
        INNER JOIN B ON A.id = B.id
        INNER JOIN C ON C.id = A.id
    WHERE
        A.date >= '2002-01-01' OR
        B.date >= '2002-01-01';

EDIT2

Below is my error message

SQL Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

Upvotes: 1

Views: 153

Answers (1)

Shan
Shan

Reputation: 81

Well I tried a few things as suggested like converting the data type and using proper date literals none of that worked. But if I remove the 3rd table completely from the query, it works. Not sure why it does that but I can always join on the third table after creating a temp table, so I think that solves my problem. Basically the query that worked is below:

    SELECT
        A.id AS my_id,
        B.is_active is_active
    FROM
        A
        INNER JOIN B ON A.id = B.id
    WHERE
        A.date >= '2002-01-01' OR
        B.date >= '2002-01-01';

Upvotes: 1

Related Questions