PROBLEM

DBAccess: Fixes for number format problems

Last Published: April 25, 2020

Solution or Workaround

SDETables and DBTable have number fields formatted incorrectly. How can I fix this?

Answer:

Any number field in an RDBMS that has a width of 9 and a precision of 0 will be represented in Arcview as a Long field type. Often data in these fields will be represented using scientific notation. You can display the entire field value by setting the number definition format with the following code:

Number.SetDefFormat("d")

Any number field in an RDBMS that has a width greater than 9 and/or a precision greater than 0 will be represented in Arcview as a Double field type. In order to alter the display of these field types, you can use a field specific definition in SQL such as TO_CHAR. The TO_CHAR function also has a number format element which converts the number value using a defined number format. See the notes below for the number format model.

For example, the SQL statement:

select (TO_CHAR(area, '99999999.990')) from states

will return a string value from a number for 8 significant digits to the left of the decimal place and 3 significant digits to the right of the decimal including those values with 0. A value of 39867.5004 will be returned as '39867.500'.

Notes:

From the Oracle Server SQL Language Reference Manual:

Element Example Description

9 9999 Number of "9"s specifies number of
significant digits returned. Blanks are
returned for leading zeros and for a
value of zero.

0 0999 Returns a leading zero or a value of
9990 zero in this position as a 0, rather
than as a blank.

$ $9999 Prefixes value with dollar sign.

B B9999 Returns zero value as blank, regard
less of "0"s in the format model.

MI 9999MI Returns "-" after negative values.
For positive values, a trailing space is
returned.

S S9999 Returns "+" for positive values and
"-" for negative values in this position.

PR 9999PR Returns negative values in . For positive values, a
leading and trailing space is returned.

D 99D99 Returns the decimal character in this
position, separating the integral and
fractional parts of a number.

G 9G999 Returns the group separator in this
position.

C C999 Returns the ISO currency symbol in
this position.

L L999 Returns the local currency symbol in
this position.

, (comma) 9,999 Returns a comma in this position.

. (period) 99.99 Returns a period in this position,
separating the integral and fractional
parts of a number.

V 999V99 Multiplies value by 10n, where n is
the number of "9"s after the
"V".

EEEE 9.999EEEE Returns value in scientific notation.

RN RN Returns upper- or lower-case Roman
numerals. Value can be an integer
between 1 and 3999.

Article ID:000001571

Software:
  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Discover more on this topic