SQL against COMP fields returns seemingly incorrect values

book

Article ID: 7138

calendar_today

Updated On:

Products

CA IDMS CA IDMS - Database CA IDMS - ADS

Issue/Introduction

An SQL select statement accessing a COMP field with an implied decimal point returns values which appear incorrect.

Cause

This is the documented behaviour. The implied decimal point is ignored.

See the Metadata Mapping section of the SQL Reference manual here:

https://docops.ca.com/ca-idms-ref/19/en/sql-reference/accessing-network-defined-databases/metadata-mapping

 

Scroll down to Data Type of Columns and see note 4 next to the COMP fields and after the table.

Environment

CA-IDMS, any supported release.

Resolution

In the following example, the network defined field COL1 is defined with PICTURE IS  S99V9. There are three rows. In DML/O, their values look like this:

02 CALCKEY..................................N  0001
02 COL1.....................................H  +0000.1

02 CALCKEY..................................N  0002
02 COL1.....................................H  +0001.0

02 CALCKEY..................................N  0003
02 COL1.....................................H  +0010.0

Accessing the same data via the SQL/Option results in this:-

SELECT * FROM SQLNSCHM.COMPTEST ORDER BY 1;
*+
*+ CALCKEY    COL1
*+ -------    ----
*+       1       1
*+       2      10
*+       3     100
*+
*+ 3 rows processed

As per the above manual reference, this is the documented behaviour.

The following view definition can be used to circumvent this from a retrieval perspective:

CREATE VIEW SQLSCHM.COMPTEST_VIEW AS
SELECT CALCKEY,
       CAST (COL1 AS DECIMAL(4,1))/10 AS COL1
  FROM SQLNSCHM.COMPTEST;

Then:-

 

SELECT * FROM SQLSCHM.COMPTEST_VIEW ORDER BY 1;
*+
*+ CALCKEY        COL1
*+ -------        ----
*+       1       0.100
*+       2       1.000
*+       3      10.000
*+
*+ 3 rows processed

Additional Information

For more information, see the following CA IDMS DocOps page:

Accessing Network-Defined Databases