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

From: tony <ramdan_at_mailexcite.com>
Date: 21 Sep 1998 14:33:08 -0500
Message-ID: <xRxN1.1693$ne.339014@newscene.newscene.com>

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?

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
>
Received on Mon Sep 21 1998 - 14:33:08 CDT