Why won't this SQL statement work?

Question

I have the following SQL-statement:

SELECT DISTINCT name FROM log WHERE NOT name = '' AND name LIKE '%.EDIT%';

It works fine on Postgres (returns all different names from log, which aren't empty and contain the string '.EDIT'). But on Oracle this statement doesn't work. Any idea why?

Answer 1

SELECT DISTINCT name FROM log WHERE NOT name = '' AND name LIKE '%.EDIT%';

1) Oracle treats '' as NULL, which means the comparison "NOT name = ''" is never true or false; use "IS NOT NULL" instead. But...

2) The second condition "name LIKE '%.EDIT%' will not match an empty string anyway, making the first condition redundant.

So re-write as:

SELECT DISTINCT name FROM log WHERE name LIKE '%.EDIT%';

Answer 2

The empty string in Oracle is equivalent to NULL, causing the comparison to fail. Change that part of the query to NAME IS NOT NULL

Answer 3

You can rewrite that query without the "NOT NAME=''" clause.

 SELECT DISTINCT name 
 FROM log
 WHERE name LIKE '%.EDIT%';

Does that work for you?

If not, in what way does it not work? Does it cause an error? Are the wrong results returned?

Please expand your question with this info :-)