Re: Diagnostic Query to avoid ORA-01722 Error

From: Ken Denny <ken_at_kendenny.com>
Date: Sat, 28 Sep 2002 16:27:18 GMT
Message-ID: <Xns9297812EB4087kendenny@65.82.44.7>

tsu_at_landacorp.com (Tom Urbanowicz) wrote in news:6d63e629.0209270721.32657a90_at_posting.google.com:

> You are correct; my oversight in mentioning that I am using the > to_number function.

You can include only those columns which contain numeric data by including the following in your "where" clause:

where translate(varchar2_column,'0123456789',null) is null

> 
> TurkBear <john.greco_at_dot.state.mn.us> wrote in message
> news:<l3i6pu49j3ir1jbl6rkka0sj3r98j6itt8_at_4ax.com>... 

>> Why an error - Max uses an implicit conversion to ASCII for the
>> entire string in the Varchar2 field and computes the Max SQL> select
>> max(empl_nm) from hr_public;
>>
>> MAX(EMPL_NM)
>> --------------------------------------------------
>> Zwart,Benjamin Jacob
>>
>> SQL>
>>
>> If you are, however, using the To_Number function first, then you
>> will get that error..
>>

-- 
Ken Denny
http://www.kendenny.com/

Received on Sat Sep 28 2002 - 11:27:18 CDT