ORA-01722 Error
-
select m_id from w_mat where ( w_mat.row_id in (Select prime_id from d_table where a_type=3 and o_type='XXX' and a_name='YYYY' and ( decode(REPLACE (TRANSLATE(TRIM(a_value),'-.0123456789', '000000000000'), '0', NULL), NULL,to_number(TRIM(a_value))) = 1 )) ) order by 1 If a_value encounters a non-numeric value, this query returns an error. Is there a way to modify the query to look for the particular value (i.e) 1 and ignore if it encounters any non-numeric value during the data fetch.. Regards, Rane
-
select m_id from w_mat where ( w_mat.row_id in (Select prime_id from d_table where a_type=3 and o_type='XXX' and a_name='YYYY' and ( decode(REPLACE (TRANSLATE(TRIM(a_value),'-.0123456789', '000000000000'), '0', NULL), NULL,to_number(TRIM(a_value))) = 1 )) ) order by 1 If a_value encounters a non-numeric value, this query returns an error. Is there a way to modify the query to look for the particular value (i.e) 1 and ignore if it encounters any non-numeric value during the data fetch.. Regards, Rane
You have several approaches to this, for example: - use REGEXP_INSTR to find nonnumeric characters. Use this inside DECODE to make the decision if to convert or not - create a small function which returns number. Inside this, use TO_NUMBER and catch the potential error and ignore it. In case of an error, return NULL (or whatever is desired) Mika
The need to optimize rises from a bad design. My articles[^]