I'm looking at a script I didn't write here. and it looks something like this:
SELECT
...
AND (
A.FIELD IN
(
...
...
)
OR B.FIELD IN
(
...
...
)
)
...
On it's own the script runs fine. No errors. But when I change it to:
SELECT
...
AND B.FIELD IN
(
...
...
)
...
Oracle throws an ORA-01722: invalid number
. How come it doesn't throw it for the first query though?
Updated
A.FIELD is a number B.FIELD is a VARCHAR2
The value is working against is a number. So I understand and agree with the error, but I wondered why it isn't thrown in the first query. But the second it is.
From Oracle's docs:
When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause an error.
See http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref481 for further explanation and examples.
They also have this separate documentation specifically mentioning that for OR
: http://www.oracle-base.com/articles/misc/short-circuit-evaluation-in-plsql.php