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'
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.attribut
I get the same results.
When I change the where clause to:
where to_char(bad_attr.attribute
I get the same results.
However, when I change the where clause to:
where to_char(bad_attr.attribute
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')
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?
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'
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.attribut
I get the same results.
When I change the where clause to:
where to_char(bad_attr.attribute
I get the same results.
However, when I change the where clause to:
where to_char(bad_attr.attribute
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')
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?
ASKER
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
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.
where trim(job_rate1) != ' ';
job_rate1 is the field created by the view as:
rpad(nvl(table1.attribute8
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.
ASKER
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.
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?
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?
sorry: and can we see the actual view sql?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Murhpy and his law at it again.
ASKER
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.
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;