Geek asks and answers

OR abbreviated in Oracle SQL?

I have a query that I need to check about 20 different columns for a value of 0.

Instead of doing:

WHERE BOOK <> 0 OR ALLO <> 0 OR ...

Is there a faster way to do this? Something like: WHERE (BOOK, ALLO, ...) <> 0

+5
source share
3 answers

Although it does not work anywhere (MySQL, SQL-Server, Postgres) and is probably not a SQL standard, it works in Oracle:

WHERE 0 <> ANY (BOOK, ALLO, ...)

Tested in SQL-Fiddle

There is also another way that is standard and works in MySQL and Postgres, but not in Oracle:

WHERE (0, 0, ...) <> (BOOK, ALLO, ...)

And another standard way (using the table value constructor) that works in Postgres and SQL-Server 2012:

WHERE 0 <> ANY (VALUES (BOOK), (ALLO), ...)
+5
source

:

WHERE (book + allo + ...) > 0
+1

, :

WHERE BOOK + ALLO + ... > 0

, :

WHERE ABS(BOOK) + ABS(ALLO) + ... > 0

, .

These solutions will only work if zero values ​​are not possible. If they are, it will be pretty dirty.

+1
source

More articles:


All Articles