Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722 Error (9i, Windows 2003)
ORA-01722 Error [message #353735] Tue, 14 October 2008 23:07 Go to next message
Messages: 1
Registered: October 2008
Karma: 0
Junior Member
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/rephrase the query to look for the particular value (in this case it is 1) and ignore if it encounters any non-numeric value during the data fetch..


[Updated on: Tue, 14 October 2008 23:54] by Moderator

Report message to a moderator

Re: ORA-01722 Error [message #353743 is a reply to message #353735] Tue, 14 October 2008 23:38 Go to previous messageGo to next message
Michel Cadot
Messages: 65153
Registered: March 2007
Location: Nanterre, France, http://...
Karma: 0
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Post a test case: create table and insert statements along with the result you want with these data.

Search for "isnumber" or "is_number" function maybe you can don't select the wrong rows (if only we could read the query, for myself I can't if it is not formatted).

Re: ORA-01722 Error [message #353748 is a reply to message #353735] Wed, 15 October 2008 00:00 Go to previous message
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Karma: 0
Senior Member

TRY to format the query as above from the next post .

btw, your query does not ignores non-numeric query.

SQL> SELECT REPLACE (TRANSLATE(TRIM('A123B'),'-.0123456789', '000000000000'),
  2  '0', NULL) FROM DUAL ;



Thumbs Up
Previous Topic: Updated rows count
Next Topic: Login to UNIX from Oracle stored procedure
Goto Forum:

Current Time: Tue Aug 22 12:00:47 CDT 2017

Total time taken to generate the page: 0.05814 seconds