CODEWITHSUNDEEP
plsqloracle-sqldeveloper
Dylan Czenski
Dylan Czenski

Reputation: 1365

Oracle PL/SQL where condition: not equal to 0 or null

I am selecting id where it cannot be equal to 0, nor can the value be (null). I wrote:

WHERE id IS NOT NULL (+) and id not in ('0')

I got 2 errors

Error(9,5): PL/SQL: SQL Statement ignored

Error(38,119): PL/SQL: ORA-00933: SQL command not properly ended I change it to:

WHERE id IS NOT NULL (+) and id is not ('0')

Same errors occurred. What should I write as the WHERE clause?

Upvotes: 3

Views: 22854

Answers (3)

krokodilko
krokodilko

Reputation: 36107

You can simplify the condition to just:

WHERE id != 0

because comparisions with NULL (using both = or != operators) always evaluates to NULL (which is treated in SQL as "false" in conditions),
therefore NULL != 0 always evaluates to FALSE and you can skip this part in this condition

WHRE id != 0 AND id IS NOT NULL

Braiam's answer Where nvl(Id,0)<>0, although correct, will prevent database from using an index on id column, and this could have bad impact on the performce.

Upvotes: 9

Andris Krauze
Andris Krauze

Reputation: 2142

(+) is for outer joins. You should write:

Where nvl(Id,0)<>0

Upvotes: 5

Rahul
Rahul

Reputation: 77866

Shouldn't it be

WHERE id IS NOT NULL
AND id != 0

Upvotes: 5

Related Questions