We help IT Professionals succeed at work.

oracle invalid number/find actual value in a field

tancat
tancat asked
on
I am using Oracle EBS 12.1.3, database 10g.  This 'appears' to be bad data, but I cannot figure out the problem.  

I have two records with an identical value in a varchar2(150) field (attribute1).  When I query the table for the good record and compare to_char(attribute1,'00.00') to null, I get a result because the value is not null.  When I query the table for the bad record and compare to_char(attribute1,'00.00') to null, I get ORA-01722: invalid number (the value is not null, it is the same as for the good record).

So I queried the table twice, once for each record:
select good.attribute1 good_attr
, bad.attribute1 bad_attr
from ( select * from table1 where person = 12345 ) good_attr
, ( select * from table1 where person = 98765 ) bad_attr
where good_attr.attribute1 = bad_attr.attribute1;
I got one row, both values are (or appear to be) '8.05'

When I use the to_char function in the where clause, I get the same results.

When I change the where clause to:
where to_char(good_attr.attribute1,'00.00') is not null;
I get the same results.

When I change the where clause to:
where to_char(bad_attr.attribute1) is not null;
I get the same results.

However, when I change the where clause to:
where to_char(bad_attr.attribute1,'00.00') is not null;
I get the invalid number error.

How can the values appear to be identical but one throws an error and the other does not?

Then I removed the value (in test) from the bad attribute1 field, ran the same query, and got the same error, even though there is nothing in that field (theoretically).

If I use to_char(attribute,'00.00') for any other attribute field on the bad record, no error.  

This is the only record that throws this error - all of the other records are fine.

I also tried using dump:
select dump(attribute1) --as well as with, 8, 10, 16, and 17
from table1
where person in (12345,98765);
and both fields appear to be identical - no hidden characters

When I use utl_match.edit_distance on the fields, the result is 0.

Any ideas on why I would get these results for observably a valid value in this field for just one record?  Am I chasing the wrong suspect?
Comment
Watch Question

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
what is the data type of attribute1 ?

why are you casting to characters in the where clause anyway?


a saragable predicate does not apply functions to the data and testing for NULL is more efficient this way:

where bad_attr.attribute1 IS NULL;

where bad_attr.attribute1 IS NOT NULL;
tancatOracle Developer

Author

Commented:
The problem originally popped up while querying a view.  The query was written as:

where trim(job_rate1) != '                    ';

job_rate1 is the field created by the view as:

rpad(nvl(table1.attribute8,' '),20,' ')||lpad(ltrim(nvl(to_char(table1.attribute1,'00.00'),' ')),5,' ')

The result of this, of course, is that when both attribute8 and attribute1 are null, we end up with a string containing 20 spaces.  Correct? - this is still a string with length and other attributes, right?  (Just making sure that I've not completely lost my mind.)

So I broke this down and the problem 'appears' to be applying the format mask with the to_char function to attribute1.  

If I change the query of the view to:

select * from table1_v
 where job_rate1 is not null;

then I still get the invalid number error.

The problem exists in both our production system and our test system, which was recently cloned from production.
tancatOracle Developer

Author

Commented:
Also, it doesn't matter if I compare job_rate1 to null, 0, '0', or the actual value in the field - it always throws the invalid number error.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
and what is the data type of that field?

e.g. varchar2(20)

I presume it is nullable

have there been changes in DDL on that table recently?
any function based indexes on that bad field?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
sorry: and can we see the actual view sql?
Oracle Developer
Commented:
It turns out that I had the correct column but the wrong row.  Oracle uses a lot of date-tracking in the tables, and it wasn't the "current" record that had the problem, it was the previous record.  

The solution was to have a non-developer look over my shoulder while I was explaining the problem, and he noticed that the data in a different row was wrong.  <sigh>

A long term solution will be to keep the users from entering 8:00 instead of 8.00.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Murhpy and his law at it again.
tancatOracle Developer

Author

Commented:
I did a whole bunch of analysis only to have a colleague at work discover that I was focusing on the wrong row of data.