SQL against COMP fields returns seemingly incorrect values

Document ID:  TEC1002184
Last Modified Date:  06/14/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA IDMS

Releases

  • CA IDMS:Release:18.5
  • CA IDMS:Release:19.0

Components

  • CA IDMS/DB:IDMS
  • CA IDMS SQL:SQLOPT
Problem:

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

Environment:
CA-IDMS, any supported release.
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.

Workaround:

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

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >

Chat with CA

Just give us some brief information and we'll connect you to the right CA ExpertCA sales representative.

Our hours of availability are 8AM - 5PM CST.

All Fields Required

connecting

We're matching your request.

Unfortunately, we can't connect you to an agent. If you are not automatically redirected please click here.

  • {{message.agentProfile.name}} will be helping you today.

    View Profile


  • Transfered to {{message.agentProfile.name}}

    {{message.agentProfile.name}} joined the conversation

    {{message.agentProfile.name}} left the conversation

  • Your chat with {{$storage.chatSession.messages[$index - 1].agentProfile.name}} has ended.
    Thank you for your interest in CA.


    Rate Your Chat Experience.

    {{chat.statusMsg}}

agent is typing