为什么这个SQL语句不起作用？

I have the following SQL-statement:

我有以下SQL语句:

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?

它在Postgres上运行正常(从日志返回所有不同的名称,它们不是空的并且包含字符串'.EDIT')。但在甲骨文这个声明不起作用。知道为什么吗?

3 个解决方案

#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...

1)Oracle将''视为NULL,这意味着比较“NOT name =''”永远不会为true或false;改为使用“IS NOT NULL”。但...

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

2)第二个条件“name LIKE'%。EDIT%'无论如何都不匹配空字符串,使第一个条件变得多余。

So re-write as:

所以重写为:

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

#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

Oracle中的空字符串等效于NULL,导致比较失败。将查询的那部分更改为NAME IS NOT NULL

#3

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

您可以在不使用“NOT NAME =''”子句的情况下重写该查询。

 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 :-)

请使用以下信息扩展您的问题:-)

#1

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