SQL against COMP fields returns seemingly incorrect values

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

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

Environment

IDMS, all supported releases.

Cause

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

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

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

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 behavior.

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

Accessing Network-Defined Databases