How many pre-decimal positions/integer digits has a DECimal (5,3) defined field ?

[Peter Blatt]

Does 5 represent the total numer of digits (including the fractional portion) or only the number of places
BEFORE the decimal point? Moreover does the number include the decimal point?

Are there differences between the databases servers ?

Peter

[Hans]

Re: How many pre-decimal positions/integer digits has a DECimal (5,3) defined field ?

peterb@gmx.net (Peter Blatt) wrote in message news:<ck4ale$n5 t$00$1@news.t-online.com>...

Does 5 represent the total numer of digits (including the fractional portion) or only the number of places
BEFORE the decimal point? Moreover does the number include the decimal point?
>
Are there differences between the databases servers ?
>
Peter

In Oracle, you would normally use the 'NUMBER(5,3)' declaration
instead of 'DECIMAL(5,3)'. It results in 5 digits being stored, with
the decimal place implied at position 3 - resulting in 6 'printer'
positions.

In Oracle, you can also specify 'NUMBER(5,-3)' which stores 5 digits
and puts the decimal 3 'zeros' after the last digit, giving you a
column or variable that displays 'thousands'.

Finally, in Oracle, the traditional internal representation of a
number is BCD - Binary Coded Decimal - with 2 digits per byte, up to
38 digits. Other variations, including IEEE Foating Point numerics
are possible as well.

If you need more details for the Oracle side, go to
http://docs.oracle.com for all online documentation, and look for the
SQL Reference Manual for the version(s) of interest. Excrutiating
detail is available in Chapter 1 under Datatypes.

Each RDBMS is exactly the same, only different. The 'only different'
is very subtle but significant enough that a generic application can
not swap out the back end without experienceing some negative impact -
frequently in scalability.

HTH
/Hans

BTW: comp.databases. oracle is a dead newsgroup, carried by only a few
ISPs. The question only needs to go to comp.database.o racle.misc (one
of the comp.databases. oracle.* heirarchy) as described in the Charter
available at http://orafaq.com

[Ed prochak]

Re: How many pre-decimal positions/integer digits has a DECimal (5,3) defined field ?

peterb@gmx.net (Peter Blatt) wrote in message news:<ck4ale$n5 t$00$1@news.t-online.com>...

Does 5 represent the total numer of digits (including the fractional portion) or only the number of places
BEFORE the decimal point? Moreover does the number include the decimal point?
>
Are there differences between the databases servers ?
>
Peter

This question is best asked in comp.databases which I added to the
list.
(but GOOGLE doesn't let me set the Follow-up: option)

IF you are asking about SQL databases, then the definition is
DECIMAL { (precision[,scale]) ]
where "precision is the total number of significant digits used to
express the number; the scale is the number of significant digits to
the right of the decimal point"

The quote is not directly from the standard. It's from SQL Instant
Reference published by SYBEX (C1993 so it is getting a little old).

For your last question, it seems database servers that claim to
support SQL would have to adhere to that definition.

HTH, both you and other readers.
Ed

[Alex Filonov]

Re: How many pre-decimal positions/integer digits has a DECimal (5,3) defined field ?

peterb@gmx.net (Peter Blatt) wrote in message news:<ck4ale$n5 t$00$1@news.t-online.com>...

Does 5 represent the total numer of digits (including the fractional portion) or only the number of places
BEFORE the decimal point? Moreover does the number include the decimal point?
>
Are there differences between the databases servers ?
>
Peter

In Oracle:

5 represents total number of decimal digits, not including decimal point.

[Knut Stolze]

Re: How many pre-decimal positions/integer digits has a DECimal (5,3) defined field ?

Alex Filonov wrote:

peterb@gmx.net (Peter Blatt) wrote in message
news:<ck4ale$n5 t$00$1@news.t-online.com>...

>Does 5 represent the total numer of digits (including the fractional
>portion) or only the number of places BEFORE the decimal point? Moreover
>does the number include the decimal point?

According to the SQL standard (SQL99), subclause 4.5.1 (page 22) says:
-----------------
An exact numeric value has a precision and a scale. The precision is a
positive integer that determines the number of significant digits in a
particular radix (binary or decimal). The scale is a non-negative integer.
A scale of 0 (zero) indicates that the number is an integer. For a scale
of S, the exact numeric value is the integer value of the significant
digits multiplied by 10^(-s).
-----------------

That makes it absolutely clear that for SQL database systems the precision
(5 in the example above) is the total number of digits, including the
fractional portion and without the decimal character.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Tibor Karaszi]

Re: How many pre-decimal positions/integer digits has a DECimal (5,3) defined field ?

Each RDBMS is exactly the same, only different. The 'only different'

is very subtle but significant enough that a generic application can
not swap out the back end without experienceing some negative impact -
frequently in scalability.

This is why I prefer NUMERIC instead of DECIMAL.

Per ANSI SQL, for DECIMAL, the RDBMS is allowed you to give you a higher precision than you asked
for. NUMERIC is required to give you the precision you ask for.

In SQL Server, they are the same (they both gives you exactly what you ask for). But by using
NUMERIC, I would have a consistent behavior across products (assuming the other product adheres to
the ANSI SQL standard).

--
Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

"Hans" <forbrich@gmail .comwrote in message news:bd0e88c6.0 410080807.63728 ec8@posting.goo gle.com...

peterb@gmx.net (Peter Blatt) wrote in message news:<ck4ale$n5 t$00$1@news.t-online.com>...

>Does 5 represent the total numer of digits (including the fractional portion) or only the number
>of places
>BEFORE the decimal point? Moreover does the number include the decimal point?
>>
>Are there differences between the databases servers ?
>>
>Peter

>
>
In Oracle, you would normally use the 'NUMBER(5,3)' declaration
instead of 'DECIMAL(5,3)'. It results in 5 digits being stored, with
the decimal place implied at position 3 - resulting in 6 'printer'
positions.
>
In Oracle, you can also specify 'NUMBER(5,-3)' which stores 5 digits
and puts the decimal 3 'zeros' after the last digit, giving you a
column or variable that displays 'thousands'.
>
Finally, in Oracle, the traditional internal representation of a
number is BCD - Binary Coded Decimal - with 2 digits per byte, up to
38 digits. Other variations, including IEEE Foating Point numerics
are possible as well.
>
If you need more details for the Oracle side, go to
http://docs.oracle.com for all online documentation, and look for the
SQL Reference Manual for the version(s) of interest. Excrutiating
detail is available in Chapter 1 under Datatypes.
>
Each RDBMS is exactly the same, only different. The 'only different'
is very subtle but significant enough that a generic application can
not swap out the back end without experienceing some negative impact -
frequently in scalability.
>
HTH
/Hans
>
BTW: comp.databases. oracle is a dead newsgroup, carried by only a few
ISPs. The question only needs to go to comp.database.o racle.misc (one
of the comp.databases. oracle.* heirarchy) as described in the Charter
available at http://orafaq.com

[Tibor Karaszi]

Re: How many pre-decimal positions/integer digits has a DECimal (5,3) defined field ?

Yes, but page 125 states that for DECIMAL, the product can give you a higher precision than asked
for:

23) DECIMAL specifies the data type exact numeric, with the decimal scale specified by the <scale>

and the implementation-defined decimal precision equal to or greater than the value of the

specified <precision>.


--
Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

"Knut Stolze" <stolze@de.ibm. comwrote in message news:ck75f2$6dd $1@fsuj29.rz.un i-jena.de...

Alex Filonov wrote:
>

>peterb@gmx.net (Peter Blatt) wrote in message
>news:<ck4ale$n 5t$00$1@news.t-online.com>...

>>Does 5 represent the total numer of digits (including the fractional
>>portion) or only the number of places BEFORE the decimal point? Moreover
>>does the number include the decimal point?

>
According to the SQL standard (SQL99), subclause 4.5.1 (page 22) says:
-----------------
An exact numeric value has a precision and a scale. The precision is a
positive integer that determines the number of significant digits in a
particular radix (binary or decimal). The scale is a non-negative integer.
A scale of 0 (zero) indicates that the number is an integer. For a scale
of S, the exact numeric value is the integer value of the significant
digits multiplied by 10^(-s).
-----------------
>
That makes it absolutely clear that for SQL database systems the precision
(5 in the example above) is the total number of digits, including the
fractional portion and without the decimal character.
>
--
Knut Stolze
Information Integration
IBM Germany / University of Jena