Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Automatic rounding of values

7 posts in Windows NT Last posting was on 1998-01-14 17:22:47.0Z
Glauco Rocha Posted on 1997-12-22 18:42:54.0Z
From: "Glauco Rocha" <rocha@senior.com.br>
Subject: Automatic rounding of values
Date: Mon, 22 Dec 1997 16:42:54 -0200
Lines: 16
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Message-ID: <#CaHZAwD9GA.137@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5340
Article PK: 1081417

Sybase SQL Server Release 11.0.1. doesn't make rounding of values, and it
accuses mistake. In Sybase AnyWhere 5.0 or Oracle 7.3 or in another banks he
makes this rounding.

I have an application in Delphi 3.02 that accuses the mistake Scale error
during implicit
conversion of Numeric Value 12.220000001, because 12.22 cannot be expressed
in Scientific Notacion.

As I do to correct this problem.

Glauco
rocha@senior.com.br


Kevin Campbell Posted on 1998-01-02 18:59:05.0Z
Message-ID: <34AD38F9.E71D5FA3@gte.net>
Date: Fri, 02 Jan 1998 10:59:05 -0800
From: Kevin Campbell <KCampbel@gte.net>
Organization: Ad Rem, Inc.
X-Mailer: Mozilla 4.04 [en] (WinNT; I)
MIME-Version: 1.0
To: Glauco Rocha <rocha@senior.com.br>
Subject: Re: Automatic rounding of values (follwo up)
References: <#CaHZAwD9GA.137@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 15
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5321
Article PK: 1081399

The Dynatext has the following on this topic:


During implicit conversions to numeric or decimal types, loss of scale
generates a scale error. Use the arithabort numeric_truncation option to
determine how serious such an error is considered. The default setting,
arithabort numeric_truncation on, aborts the statement that causes the
error, but continues to process other statements in the transaction or
batch. If you set arithabort numeric_truncation off, Adaptive Server
truncates the query results and continues processing.

Hope this clarifies things.

Kevin


Glauco Rocha Posted on 1998-01-14 17:22:47.0Z
From: "Glauco Rocha" <rocha@senior.com.br>
References: <#CaHZAwD9GA.137@forums.powersoft.com> <34AD38F9.E71D5FA3@gte.net>
Subject: Re: Automatic rounding of values (follwo up)
Date: Wed, 14 Jan 1998 15:22:47 -0200
Lines: 14
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Message-ID: <bXYqaeQI9GA.138@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5299
Article PK: 1081371

With set arithabort off Ok, but I wanted that rounding, doesn't truncate.

>During implicit conversions to numeric or decimal types, loss of scale
>generates a scale error. Use the arithabort numeric_truncation option to
>determine how serious such an error is considered. The default setting,
>arithabort numeric_truncation on, aborts the statement that causes the
>error, but continues to process other statements in the transaction or
>batch. If you set arithabort numeric_truncation off, Adaptive Server
>truncates the query results and continues processing.

Glauco


Glauco Rocha Posted on 1998-01-14 17:21:46.0Z
From: "Glauco Rocha" <rocha@senior.com.br>
References: <#CaHZAwD9GA.137@forums.powersoft.com> <34AD38F9.E71D5FA3@gte.net>
Subject: Re: Automatic rounding of values (follwo up)
Date: Wed, 14 Jan 1998 15:21:46 -0200
Lines: 17
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Message-ID: <6XQAZeQI9GA.137@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5300
Article PK: 1081372

With set arithabort off Ok, but I wanted that rounding, doesn't truncate.

>During implicit conversions to numeric or decimal types, loss of scale
>generates a scale error. Use the arithabort numeric_truncation option to
>determine how serious such an error is considered. The default setting,
>arithabort numeric_truncation on, aborts the statement that causes the
>error, but continues to process other statements in the transaction or
>batch. If you set arithabort numeric_truncation off, Adaptive Server
>truncates the query results and continues processing.
>
>Hope this clarifies things.
>
>Kevin

Glauco


Bret Halford Posted on 1997-12-23 19:22:14.0Z
Message-ID: <34A00F66.1BBF@sybase.com>
Date: Tue, 23 Dec 1997 12:22:14 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: Glauco Rocha <rocha@senior.com.br>
Subject: Re: Automatic rounding of values
References: <#CaHZAwD9GA.137@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 32
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5337
Article PK: 1081415


Glauco Rocha wrote:
>
> Sybase SQL Server Release 11.0.1. doesn't make rounding of values, and it
> accuses mistake. In Sybase AnyWhere 5.0 or Oracle 7.3 or in another banks he
> makes this rounding.
>
> I have an application in Delphi 3.02 that accuses the mistake Scale error
> during implicit
> conversion of Numeric Value 12.220000001, because 12.22 cannot be expressed
> in Scientific Notacion.
>
> As I do to correct this problem.

What datatype are you using to store the value? It sounds
like you are using FLOAT, which indeed cannot store many
values exactly. If you store 12.22 in a float, the closest
representation float can manage is 12.220000000000001.

============================================================
Note that this is not a Sybase-specific issue. If you
compile this simple c program, you will see the same result:

main()
{
float c ;
c = 12.22;
printf("c is %48.24f \n",c);
};
=============================================================

Using the NUMERIC datatype may resolve your problem, as it
does store exact values.
--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


heto Posted on 1997-12-24 02:17:31.0Z
From: "heto" <hevertom@senior.com.br>
Subject: Re: Automatic rounding of values
References: <#CaHZAwD9GA.137@forums.powersoft.com> <34A00F66.1BBF@sybase.com>
Message-ID: <01bd101b$3c6734c0$1200a8c0@hevertom>
X-Newsreader: Microsoft Internet News 4.70.1155
Newsgroups: sybase.public.sqlserver.nt
Date: Tue, 23 Dec 1997 21:17:31 -0500
Lines: 46
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5335
Article PK: 1081414

Bret Halford <bret@sybase.com> escreveu no artigo
<34A00F66.1BBF@sybase.com>...

> Glauco Rocha wrote:
> >
> > Sybase SQL Server Release 11.0.1. doesn't make rounding of values, and
it
> > accuses mistake. In Sybase AnyWhere 5.0 or Oracle 7.3 or in another
banks he
> > makes this rounding.
> >
> > I have an application in Delphi 3.02 that accuses the mistake Scale
error
> > during implicit
> > conversion of Numeric Value 12.220000001, because 12.22 cannot be
expressed
> > in Scientific Notacion.
> >
> > As I do to correct this problem.
>
> What datatype are you using to store the value? It sounds
> like you are using FLOAT, which indeed cannot store many
> values exactly. If you store 12.22 in a float, the closest
> representation float can manage is 12.220000000000001.
>
> ============================================================
> Note that this is not a Sybase-specific issue. If you
> compile this simple c program, you will see the same result:
>
> main()
> {
> float c ;
> c = 12.22;
> printf("c is %48.24f \n",c);
> };
> =============================================================
>
> Using the NUMERIC datatype may resolve your problem, as it
> does store exact values.
> --
> Bret Halford Imagine my disappointment
> Sybase Technical Support in learning the true nature
> 3665 Discovery Drive of rec.humor.oracle...
> Boulder, CO 80303
>

Ok, But

My Variable in Delphi is Double and I have one column name NumVl, Data Type
is Number (11,2),

Comand SQL: Update Bank set NumVl = 12.2200001

In Sybase AnyWhere 5.0 Ok

In Sybase SQL Server Release 11.0.1 or 11.5
- Server Message: Number 241, Severity 16
- Scale error during implicit conversion of Numeric value '12.22000001'
to
numeric field


Glauco


Kevin Campbell Posted on 1997-12-31 15:08:06.0Z
Message-ID: <34AA5FD6.9A4DDECD@gte.net>
Date: Wed, 31 Dec 1997 07:08:06 -0800
From: Kevin Campbell <KCampbel@gte.net>
Organization: Ad Rem, Inc.
X-Mailer: Mozilla 4.04 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Re: Automatic rounding of values
References: <#CaHZAwD9GA.137@forums.powersoft.com> <34A00F66.1BBF@sybase.com> <01bd101b$3c6734c0$1200a8c0@hevertom>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 11
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5324
Article PK: 1081401

Isn't the problem simply that you are trying to store a value with too
many decimal places? You say that your column is numeric(11,2), so I
guess I'm not surprised that it complains when you try and store a
number with 8 decimal places.

Could you convert your number to 2 decimal places before storing?
Something like x = int(x * 100) / 100 would truncate it to 2 decimal
places.

Kevin