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
rane
Messages: 1
Registered: October 2008
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..

Thanks,
Rane

[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: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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).

Regards
Michel
Re: ORA-01722 Error [message #353748 is a reply to message #353735] Wed, 15 October 2008 00:00 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
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 ;

RE
--
AB

SQL>


Thumbs Up
Rajuvan
Previous Topic: Suggestion on Connect By query
Next Topic: Updated rows count
Goto Forum:
  


Current Time: Sat Dec 10 20:24:00 CST 2016

Total time taken to generate the page: 0.28575 seconds