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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
sorry: and can we see the actual view sql?
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.