Schema Collections

The Schema collections return metadata for the Advanced SQL Engine objects. The notable metadata columns for the Decimal data type are:

Schema Table

The TdDataReader.GetSchemaTable returns result set metadata. The notable metadata columns for the Decimal data type are:

Configuring Parameters

The TdParameter.Precision property reflects the maximum number of digits and it must be equal to or less than the target SQL Engine object (i.e. Column, Expression, Stored Procedure Parameters and etc.) declaration. The range of values are from 1 to 38.

The TdParameter.Scale property reflects the maximum number of digits to the right of the decimal point and it must be equal to or less than the target SQL Engine object (i.e. Column, Expression, Stored Procedure Parameters and etc.) declaration. The range of values are from 0 to 38 but it must be equal to or less than the Precision.

When a decimal number is inserted into a column defined as DECIMAL it may get truncated or rounded up depending upon the definition of the column and the TdParameter properties settings.

The decimal number will be truncated if the TdParameter.TdType property is defined as a TdType.Decimal and the scale specified in the TdParameter.Scale property is less than the actual scale of the number. For example, a column of a table is defined as DECIMAL(11, 4) and the parameter has been defined with the following properties:

TdParameter quantity = new TdParameter();
quantity.TdType = TdType.Decimal;
quantity.Precision = 10;
quantity.Scale = 0;
quantity.Value = TdDecimal.Parse("435924.9999");

The decimal that is to be inserted has an actual precision of 10 and scale of 4. The parameter has been defined with a scale of 0. Therefore, the number that will be inserted into the column will be 435924 --the number has been truncated.

It is also possible that a number will be rounded up when inserted into a decimal column. This will occur when the Precision and Scale properties of the TdParameter are 0 --or undefined--, and the actual scale of the number is greater than the scale defined for the column. For example, a column is defined as DECIMAL(10, 2), and the parameter has the following properties:

TdParameter quantity = new TdParameter();
quantity.TdType = TdType.Decimal;
quantity.Value = TdDecimal.Parse("435924.967);

The actual precision and scale of the number to be inserted is (9, 3), and the precision and scale properties of the TdParameter are are undefined. The provider will determine the precision and scale of the parameter based upon the actual number. Since the scale of the DECIMAL column is less than the scale of the parameter, Teradata will round up the number. The number that will be inserted is 435924.97.

Note
The Data Provider will adjust the scale of the parameter value to the TdParameter.Scale specified. The Data Provider truncates the digits to the right of the decimal point to the number of digits specified by the TdParameter.Scale property.

Note

The Data Provider will throw a TdException when the TdParameter.Precision property specified is less than the actual precision of the parameter value. The error message is either generated by the Data Provider (i.e. "A Value of a parameter in the collection has a precision or scale that is too large") or the SQL Engine (i.e. "[Teradata Database] [2683] Numeric overflow occurred during computation").

The following example shows how to configure a Decimal parameter using DbType type declaration and a BCL value.

TdParameter quantity = new TdParameter();

quantity.DbType = DbType.Decimal;

quantity.Precision = 38;
quantity.Scale = 2;

quantity.Value = 1000.23M;

The following example shows how to configure a Decimal parameter using TdType type declaration and a Provider Specific value. It is recommend to always set TdParameter.Precision and TdParameter.Scale properties, however the Data Provider will utilize TdDecimal.Precision and TdDecimal.Scale property values when the TdParameter.Precision and TdParameter.Scale properties are set to zero. .NET Applications can retrieve metadata from the Schema Collections or the Schema Table and apply the metadata to the TdParameter object.

TdParameter quantity = new TdParameter();

quantity.TdType = TdType.Decimal;

quantity.Precision = 38;
quantity.Scale = 2;

quantity.ProviderSpecificValue = TdDecimal.Parse("1,234,567,890,765,432,102,394,586,019,928,847.99");

Specifying Decimal as Literal

The syntax for the Decimal Literal is ±n.n. Decimal literal consist of an optional sign, up to 38 digits representing the whole and fractional components of the decimal. The Decimal point is used to separate the whole and fractional components of the Decimal literal.

Note
We recommend to always use Parameters in order to take advantage of the SQL Engine's Request Cache.

The following example shows 12,987,561.99 represented as Decimal Literal in the Command Text.

using (TdConnection cn = new TdConnection("data source=x;UserId=y;Password=z;"))
{
    cn.Open();
    TdCommand cmd = cn.CreateCommand();

    cmd.CommandText = "SELECT Id, orderDate from Order where quantity > 1298756.99  ";

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("Id={0}, orderDate={1}", reader.GetInt64(0), reader.GetDate(1));
            }
        }
    }
}

Retrieving Decimal Data

The following methods and properties return the column or parameter value as a System.Decimal structure.

Note
The Data Provider will throw a System.OverflowException when the decimal data retrieved from the Teradata base exceeds the maximum precision of the System.Decimal. The System.Decimal has a maximum precision of 29 while the SQL Engine Decimal has a maximum precision of 38. It is recommend to use TdDataReader.GetTdDecimal method to retrieve decimal objects declared with precision greater than 29.

1. TdDataReader.GetDecimal
2. TdDataReader.GetValue
3. TdDataReader.GetFieldValue<Decimal>
4. TdParameter.Value
5. TdParameter.GetValue<Decimal>

The following Provider Specific Value methods and property return the column or parameter value as a TdDecimal structure.

1. TdDataReader.TdDecimal
2. TdDataReader.GetProviderSpecificValue
3. TdDataReader.GetFieldValue<TdDecimal>
4. TdParameter.ProviderSpecificValue
5. TdParameter.GetValue<TdDecimal>

using (TdConnection cn = new TdConnection("data source=DS1;UserId=Joe;Password=XY;"))
{
    cn.Open();

    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "SELECT Id, Price from Order where Quantity > ?";
    cmd.Parameters.Add("quantity", TdType.Decimal);
    cmd.Parameters["quantity"].Precision = 38;
    cmd.Parameters["quantity"].Scale = 0;
    cmd.Parameters["quantity"].Value = 12561M;

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("[System.Decimal] Price = {0}", reader.GetDecimal(1).ToString());
                Console.WriteLine("[TdDecimal] Price = {0}", reader.GetTdDecimal(1).ToString());

            }
        }
    }
}

/* Output:
    [System.Decimal] Price = 1234567890123.78;
    [TdDecimal] Price = 1234567890123.78;
*/

See Also

Data Type Mappings

Accessor Methods for Retrieving Data

Configuring Parameters and Parameter Data Types