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

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Peter Blatt

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

    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

    #2
    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

    Comment

    • Ed prochak

      #3
      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

      Comment

      • Alex Filonov

        #4
        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.

        Comment

        • Knut Stolze

          #5
          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

          Comment

          • Tibor Karaszi

            #6
            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




            "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

            Comment

            • Tibor Karaszi

              #7
              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




              "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

              Comment

              Working...