0

There seems to be inconsistencies with how ERROR-01722 error worked, for those who don't know the issue is due to an invalid number and to fix it you'll need to wrap the number to char.

But when filtering VARCHAR2 it is stated that Oracle will convert the data of the column being filtered based on the value given to it. (see: https://stackoverflow.com/a/10422418/5337433)

Now that this is explained for some reason, the error is inconsistent. As an example I have this query:

In this example filter1 is varchar2

select * 
from table 
where filter1 = 12345 
  and filter2 = '' 
  and filter3 = '';

When this statement run there were no issues, but when you run it like this:

select * 
from table 
where filter1 = 12345 
and filter2 = '';

it errors out to ERROR-01722, im not sure why it is acting this way, and how to fix it.

gabo
  • 15
  • 4
  • 1
    I would suggest never ever ever relying on implicit casts. – Andrew Mar 21 '22 at 16:46
  • @Andrew just to make sure, so Oracle implicit casting is not that really good correct? – gabo Mar 21 '22 at 16:51
  • Well, certainly not in my opinion :) – Andrew Mar 21 '22 at 16:55
  • Those can't be your actual queries. In all versions of Oracle database, `''` is treated as `null`, and `[anything] = null` is always automatically false. The optimizer will **always** transform your `where` clauses into simply `where null is not null` (meaning, no rows will be returned from **either** query). It's OK to simplify your problem before you post here, but **DO** check that your problem is actually still present in the simplified form you plan to post. –  Mar 21 '22 at 17:29
  • @mathguy - this has always confused me about oracle. Are `column = ''`, `column = null`, and `column is null` all basically equivalent? – Andrew Mar 21 '22 at 18:07
  • @Andrew - no, they are not equivalent. `filter2 = ''` is equivalent to `filter2 = null`, and they are both always false. The correct way to test for null is `filter2 is null`, which is not always false (nor always true). –  Mar 21 '22 at 18:11
  • The reason you don't want to rely on implicit data type conversion has to do with Oracle's Cost Based Optimizer and its sequence of 1. can I or should I include the column being referenced as an indexable item and 2. coerce the coercible expression to some other type. The latter happens after the decision, which is almost always *not* what you want. Using proper data types is always the right thing to do initially do because it avoids multiple problems. And if you know your data and your app's meaning, then that should not be a difficult goal to reach. – Jeff Holt Mar 21 '22 at 18:19

1 Answers1

1

When you compare a varchar column to a number, Oracle will try to convert the column's content to a number, not the other way round (because 123 could be stored as '0123' or '00123')

In general you should always use constant values that match the data type of the column you compare them with. So it should be:

 where filter1 = '12345'

However if you are storing numbers in that column, you should not define it as varchar - it should be converted to a proper number column.

The reason the error doesn't show up "consistently" is that you seem to have some values that can be converted to a number and some can't. It depends on other conditions in the query if the those values are included or not.

Additionally: empty strings are converted to NULL in Oracle. So the condition filter2 = '' will never be true. You will have to use filter2 is null if you want to check for an "empty" column.