Re: Wierd problem, max(column_name) gives different result than max(to_number(column_name))

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Sep 1998 19:45:06 GMT
Message-ID: <360eaac2.23525257@192.86.155.100>

A copy of this was sent to ramdan_at_mailexcite.com (tony) (if that email address didn't require changing) On 21 Sep 1998 14:33:08 -0500, you wrote:

>Hmm, confused. Which is trylu the highest eqnum?
>I checked and found the following
>
>MAX(TO_NUMBER(EQNUM))
>=====================
> 1080101
>
>MAX(EQNUM)
>==========
>1044241
>
>
>
>EQNUM LENGTH(EQNUM)
>========== =============
>01080101 8
>1044241 7
>
>
>One appears to have a leading zero, which is taken off by the to_number
>function. I have to decide which is the highest eqnum to seed a conversion
>program. Since eqnum is a varchar field, do i take max(eqnum) as highest(even
>though numerically it is not, but a select eqnum from equipment order by eqnum
>desc lists it as the highest) or to_number(max(eqnum)) as highest.
>
>Is the collating order the root problem here? What is procedure to determine
>highest number. should we do a ltrim on eqnums to get rid of leading zeros?
>

no, the collating order is not the root problem here. the problem is you are trying to apply numeric sorting characteristics to strings (not numbers) and expecting the same results.

You'll have to decide whether to treat this column as a STRING (in which case, 1044241 > 01080101 just like 'a' > 'boy') or as a NUMBER in which case, 01080101
> 1044241.

This is a good example of why choosing the correct datatype is somewhat important...

>
>In article <360a8400.13603250_at_192.86.155.100>, tkyte_at_us.oracle.com wrote:
>>A copy of this was sent to ramdan_at_mailexcite.com (tony)
>>(if that email address didn't require changing)
>>On 21 Sep 1998 10:31:02 -0500, you wrote:
>>
>>>
>>>I have a table that includes an equipment number (eqnum). It is a varchar
>>>field. When I do a
>>>
>>> select max(eqnum) I get 1044241
>>>
>>> when i do a
>>>
>>> select max(to_number(eqnum) I get 1080101
>>>
>>>the lengths are the same a
>>>SELECT max(eqnum), max(to_number(eqnum)), length(max(eqnum)),
>>>length(max(to_number(eqnum)))
>>>gives
>>>1044241 1080101 7 7
>>>
>>>why the difference??
>>>isn't a number in a varchar field the same as a number in an integer field?
>>
>>nope, no way, not a chance....
>>
>>>
>>>respond to group or ramdan_at_mailexcite.com
>>
>>
>>I bet 1080101 has a leading blank.
>>
>>SQL> create table t1 ( eqnum varchar2(10) );
>>Table created.
>>
>>SQL> insert into t1 values ( '1044241' );
>>1 row created.
>>
>>SQL> insert into t1 values ( ' 1080101' );
>>1 row created.
>>
>>SQL> select max( eqnum ) from t1;
>>
>>MAX(EQNUM)
>>----------
>>1044241
>>
>>SQL> select max( to_number(eqnum) ) from t1;
>>
>>MAX(TO_NUMBER(EQNUM))
>>---------------------
>> 1080101
>>
>>SQL> select max(eqnum) max1, max(to_number(eqnum)) max2,
>> 2 length(max(eqnum)) l1, length(max(to_number(eqnum))) l2
>> 3 from t1
>> 4 /
>>
>>MAX1 MAX2 L1 L2
>>---------- ---------- ---------- ----------
>>1044241 1080101 7 7
>>
>>
>>SQL> select eqnum, length(eqnum) from t1;
>>
>>EQNUM LENGTH(EQNUM)
>>---------- -------------
>>1044241 7
>> 1080101 8
>>
>>
>>The length of the to_number(eqnum) might be 7, but the length of eqnum is
>>different....
>>
>>
>>Thomas Kyte
>>tkyte_at_us.oracle.com
>>Oracle Government
>>Herndon VA
>>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Sep 21 1998 - 14:45:06 CDT