Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Gisung
Advisor
Advisor
0 Kudos

[Problem]

A numeric error is returned when executing a select query via isql.

Error :-

========

1> select * from gjang

     CT-LIBRARY error: ct_results(): protocol specific layer: external error: There is a tds protocol error.

      An invalid numeric precision was received.

[Reproduction]

1. create a table with a default value of numeric on column.

    isql -Udba -Psql -Shpita_iqdemo

    1> create table gjang(c1 numeric)

    2> go

2. execute a select query via isql.

    1> select * from gjang

    2> go

[Cause]

An OpenClient (OC) CT-Lib(isql) is mainly for ASE, which has numeric/decimal datatype limited to scale/precision of 38/38.

The error below is returned when two numeric/decimal values exceeded the scale/precision limits of OpenClient(77/77).

[Error]

   An invalid numeric precision was received.

Please refer to below Numeric Data Types.

Numeric Data Types

(SyBooks Online)

Adaptive Server, SQL Anywhere, and SAP Sybase IQ have different default precision and scale.

  • In Adaptive Server, the default is precision 18 scale 0.
  • In SQL Anywhere, the default is precision 30 scale 6.
  • In SAP Sybase IQ, the default is precision 126 scale 38. Because these defaults are too large for TDS and for some client tools, always specify a precision and scale for SAP Sybase IQ exact numeric types.

[Solution]

  1. When creating a table, use NUMERIC and DECIMAL datatypes with precision less than or equal to 77.

      Ex) create table gjang(c1 numeric(77));

  2. Use an IQ's default client such as dbisql /dbisqlc instead of isql.

  3. Set the two precision options below to less than or equal to 77.

     - MAX_CLIENT_NUMERIC_SCALE

     - MAX_CLIENT_NUMERIC_PRECISION

      Ex) 1> set temporary option MAX_CLIENT_NUMERIC_PRECISION=76

            2> go

HTH

======

Gi-Sung Jang

1 Comment