437,925 Members | 1,778 Online Need help? Post your question and get tips & solutions from a community of 437,925 IT Pros & Developers. It's quick & easy.

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

 P: n/a 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 Nov 12 '05 #1
6 Replies

 P: n/a pe****@gmx.net (Peter Blatt) wrote in message news:... 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.oracle.misc (one of the comp.databases.oracle.* heirarchy) as described in the Charter available at http://orafaq.com Nov 12 '05 #2

 P: n/a pe****@gmx.net (Peter Blatt) wrote in message news:... 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 Nov 12 '05 #3

 P: n/a pe****@gmx.net (Peter Blatt) wrote in message news:... 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. Nov 12 '05 #4

 P: n/a Alex Filonov wrote: pe****@gmx.net (Peter Blatt) wrote in message news:... 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 Nov 12 '05 #5

 P: n/a > 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" wrote in message news:bd**************************@posting.google.c om... pe****@gmx.net (Peter Blatt) wrote in message news:... 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.oracle.misc (one of the comp.databases.oracle.* heirarchy) as described in the Charter available at http://orafaq.com Nov 12 '05 #6

 P: n/a 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 and the implementation-defined decimal precision equal to or greater than the value of the specified . -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Knut Stolze" wrote in message news:ck**********@fsuj29.rz.uni-jena.de... Alex Filonov wrote: pe****@gmx.net (Peter Blatt) wrote in message news:... 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 Nov 12 '05 #7

### This discussion thread is closed

Replies have been disabled for this discussion. 