'ORA-12801: error signaled in parallel query server P01H, ORA-01722: invalid number

as i am using below the condition to change the data_type TO_NUMBER but facing the error while TESTING

ORA-12801: error signaled in parallel query server P01H, ORA-01722: invalid number

to_number(trunc(CASE WHEN $Aggregation.TP_SYS_NM='CT' THEN $BCRS_TCDR_CT.rwa WHEN $Aggregation.TP_SYS_NM in ('CT1','CT2') THEN $BCRS_TCDR_CT1_CT2.rwa ELSE NULL END))

please help on this issue. thanks in advance.

sqloracleplsqloracle11gplsqldeveloper


Solution 1:[1]

As long as you are on 12c or above, you can use VALIDATE_CONVERSION or extensions to TO_NUMBER to guard against this, eg

select to_date(created_date, 'dd-mon-yyyy'), ...
from   STAGING_SALES
where  validate_conversion(
         created_date as date, 'dd-mon-yyyy'
       ) = 1;

which returns 0 or 1 depending on validity, or you can choose a default, eg

select SALES_AMT,
        TO_NUMBER(SALES_AMT
                 DEFAULT -1 ON CONVERSION ERROR) conv_sale
from   STAGING_SALES;

More examples and details at this article

https://asktom.oracle.com/Misc/oramag/better-tools-for-better-data.html

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Connor McDonald

Related Questions