oracle invalid number/find actual value in a field

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?
tancatOracle DeveloperAsked:
Who is Participating?
 
tancatOracle DeveloperAuthor 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.
0
 
PortletPaulfreelancerCommented:
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;
0
 
tancatOracle DeveloperAuthor 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tancatOracle DeveloperAuthor 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.
0
 
PortletPaulfreelancerCommented:
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?
0
 
PortletPaulfreelancerCommented:
sorry: and can we see the actual view sql?
0
 
PortletPaulfreelancerCommented:
Murhpy and his law at it again.
0
 
tancatOracle DeveloperAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.