Oracle error: Invalid Number Cause

[boxzzz]

hi,

Can anyone figure out why I'm getting this error message after the query runs for 3 hours. ( see query below)
sub_business code is numeric

Thanks


ERROR at line 1:
ORA-12801: error signaled in parallel query server P176
ORA-01722: invalid number

ORA-01722: invalid number Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates. Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.



select 'AAA ',
case when sub_business in (1,9) then 'AAA'
when sub_business in (5,316,317) then 'BBB'
when sub_business in (7) then 'CCC'
when sub_business between 1000 and 4499 then 'DDD'
else 'Unknown' End) ; sub_business,
count(distinct cons_link)
from (select sub_business,
cons_link
from acct_tbl
where stat_extn is null
union
select sub_business,
cons_link
from f_acct_tbl
where stat_extn is null)
where cons_link in
(select distinct(cons_link)
from acct_tbl
where stat_extn is null
and sub_business in (1, 9))
group by (case when sub_business in (1,9) then 'AAA'
when sub_business in (5,316,317) then 'BBB'
when sub_business in (7) then 'CCC'
when sub_business between 1000 and 4499 then 'DDD'
else 'Unknown' End) ;
order by (case when sub_business in (1,9) then 'AAA'
when sub_business in (5,316,317) then 'BBB'
when sub_business in (7) then 'CCC'
when sub_business between 1000 and 4499 then 'DDD'
else 'Unknown' End) ;

 

[sem]

In most cases this error occures when non-numeric field is compared with numeric one. You may check cons_link type in acct_tbl and f_acct_tbl. Then, if some tables are in fact views, check their definitions: real execution path may differ significantly from that looking obvious, thus even successfull select * from some_view does not guarantee the same for select * from some_view where some_condition

Regards, Dima

http://seminihin.narod.ru