CODEWITHSUNDEEP
sqloracle-database
M. Mill
M. Mill

Reputation: 57

WHERE is NULL clause returning NON NULL Values

This is baffling to me. I have the following query:

SELECT *
  FROM table.contract
 WHERE end_date IS NULL
   AND LOWER(NAME) LIKE 'name'
    OR LOWER(NAME) LIKE '%name1%'
    OR LOWER(NAME) LIKE '%name2%'

As it should be, it should only return rows where the END_DATE is NULL, but I'm getting rows that have a date in them.

The data type is DATE and the data default is (null) so I have no idea why it's returning some rows with dates as well. Am I just having a brain fart? It is Friday so I apologize if I'm missing something simple.

Any help would be greatly appreciated!

EDIT: I am quite an idiot. I forgot my parenthesis, the code should be as follows:

SELECT *
  FROM table.contract
 WHERE end_date IS NULL
   AND (LOWER(NAME) LIKE 'name'
    OR LOWER(NAME) LIKE '%name1%'
    OR LOWER(NAME) LIKE '%name2%')

Upvotes: 1

Views: 101

Answers (1)

ShoeLace
ShoeLace

Reputation: 3576

you appear to be missing some () to ensure the correct precendence between your "AND"s and your "OR"s.

eg

SELECT *
  FROM table.contract
 WHERE end_date IS NULL
   AND (LOWER(NAME) LIKE 'name'
    OR LOWER(NAME) LIKE '%name1%'
    OR LOWER(NAME) LIKE '%name2%')

Upvotes: 1

Related Questions