Re: Problem with to_number in update

Luke, seem you have the wrong number format. Check this

SQL> select to_number('0.625') from dual ;

TO_NUMBER('0.625')

               .625

SQL> set numformat 9G999 -- no decimal digits. SQL> select to_number('0.625') from dual ;

TO_NUMBER('0.625')

                  1

SQL> set numformat 9G999D999 -- decimal digits 3. SQL> select to_number('0.625') from dual ;

TO_NUMBER('0.625')

               .625

SQL> set numformat 9G999D99 -- only two decimal digits SQL> select to_number('0.625') from dual ;

TO_NUMBER('0.625')

                .63 -- this is rounded.

/Svend Jensen

Luke Airig wrote:
> Platform : SunOS ngserver 5.8 Generic_108528-14 sun4u sparc
> SUNW,Ultra-80
> Oracle version: Oracle8i Enterprise Edition Release 8.1.7.2.0
>
> Can anyone give me a hint as to why the update at the end of this
> post appears to be rounding?
>
> TIA
>
>
> SQL->SET NULL §
>
> SQL->desc dosage_tbl
> Name Null? Type
> ----------------------------------------- -------- -------------
> DRUG_DOSAGE VARCHAR2(8)
> DRUG_DOSAGE_NBR NUMBER(38)
>
>
> -----------------------------------------------------------------
> SQL->select a.drug_dosage
> , a.drug_dosage_nbr
> from dosage_tbl a
> ;
>
>
> DRUG_DOS DRUG_DOSAGE_NBR
> -------- ---------------
> .025 §
> .625 §
> 120 §
> 180 §
> 120 §
> 5 §
> 5 §
> 450 §
> 250 §
> 2 §
>
> 10 rows selected.
>
> -----------------------------------------------------------------
> SQL->select a.drug_dosage
> , to_number ( a.drug_dosage )
> from dosage_tbl a
> ;
>
> DRUG_DOS TO_NUMBER(A.DRUG_DOSAGE)
> -------- ------------------------
> .025 .025
> .625 .625
> 120 120
> 180 180
> 120 120
> 5 5
> 5 5
> 450 450
> 250 250
> 2 2
>
> 10 rows selected.
>
> -----------------------------------------------------------------
> SQL->update dosage_tbl a
> set a.drug_dosage_nbr = to_number ( a.drug_dosage )
> where a.rowid = ( select rowid
> from dosage_tbl b
> where a.rowid = b.rowid
> )
> ;
>
> 10 rows updated.
>
> -----------------------------------------------------------------
> SQL->select a.drug_dosage
> , a.drug_dosage_nbr
> from dosage_tbl a
> ;
>
> DRUG_DOS DRUG_DOSAGE_NBR
> -------- ---------------
> .025 0 <---------huh?
> .625 1 <---------huh?
> 120 120
> 180 180
> 120 120
> 5 5
> 5 5
> 450 450
> 250 250
> 2 2
>
> 10 rows selected.
Received on Tue Feb 18 2003 - 19:36:07 CET