DB005154 accessing duplicate database record element in SQL

Document ID : KB000010390
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

It is possible to have an element appearing multiple times in the same database record (for example as a subordinate element to a group field).

If you try to reference such an element in an SQL statement, you get the following error:-

*+ DB005154 T58 C-4M324: Ambiguous column SUB1

Background:

By default, when you try to create a network defined database record with duplicate element names, the attempt to validate the schema fails with this:

*+ E DC643177  ELEMENT SUB1 NAME IS NOT UNIQUE, DUPLICATES ELEMENT      WORD  2
*+ E DC643057  SCHEMA HAS ERRORS - SUBSCHEMA PROCESSING NOT ALLOWED     WORD  2
*+ W DC601017  FORWARD SPACING TO NEXT PERIOD                           WORD  2

To address this, you should enable RHDCOPTF optional bit 80.

Then, the VALIDATE of the network schema works but with the following warning:

*+ W DC643205  RECORD R1 HAS A NON-UNIQUE ELEMENT SUB1, ELEMENT         WORD  2
*+ - DC643205  CANNOT BE REFERENCED BY CA IDMS PRODUCTS

Accessing such fields with the CA-IDMS SQL/Option results in the DB005154.

Environment:
CA-IDMS. All supported releases.
Instructions:

Consider the following record layout:

MOD  RECORD NAME IS R1 VERSION IS 1
RECORD NAME SYNONYM IS R1 VERSION 1
.
RECORD ELEMENT IS CALCKEY VERSION 1
PICTURE IS  9(4)
.
RECORD ELEMENT IS GRP1 VERSION 1
.
SUBORDINATE ELEMENT IS SUB1 VERSION 1
PICTURE IS  X(4)
.
SUBORDINATE ELEMENT IS SUB2 VERSION 1
PICTURE IS  X(4)
.
RECORD ELEMENT IS GRP2 VERSION 1
.
SUBORDINATE ELEMENT IS SUB3 VERSION 1
PICTURE IS  X(4)
.
SUBORDINATE ELEMENT IS SUB1 VERSION 1
PICTURE IS  X(4)
.

Note that element SUB1 appears twice.

An SQL statement which does not directly reference SUB1 works:-

SELECT * FROM SQLSCHM.R1;
*+
*+ CALCKEY  SUB1  SUB2  SUB3  SUB1
*+ -------  ----  ----  ----  ----
*+       1  0001  0002  0003  0004
*+
*+ 1 row processed

But a statement which does directly reference SUB1 fails:-

SELECT CALCKEY, SUB1 FROM SQLSCHM.R1;
*+ Status = -4       SQLSTATE = 42501        Messages follow:
*+ DB005000 T58 C0M324: Message for module IDMSOCF, SQL statement number : 1.
*+ DB005154 T58 C-4M324: Ambiguous column SUB1

The solution is to add a SYNONYM to the record definition in IDD and to the duplicate element:-

MOD  RECORD NAME IS R1 VERSION IS 1
RECORD NAME SYNONYM IS R1 VERSION 1
RECORD NAME SYNONYM IS R1SQLSYN VERSION 1
LANGUAGE IS SQL
.
RECORD ELEMENT IS CALCKEY VERSION 1
PICTURE IS  9(4)
.
RECORD ELEMENT IS GRP1 VERSION 1
.
SUBORDINATE ELEMENT IS SUB1 VERSION 1
PICTURE IS  X(4)
.
SUBORDINATE ELEMENT IS SUB2 VERSION 1
PICTURE IS  X(4)
.
RECORD ELEMENT IS GRP2 VERSION 1
.
SUBORDINATE ELEMENT IS SUB3 VERSION 1
PICTURE IS  X(4)
.
SUBORDINATE ELEMENT IS SUB1 VERSION 1
PICTURE IS  X(4)
ELEMENT NAME SYNONYM IS SUB4
FOR RECORD SYNONYM R1SQLSYN VERSION 1
.

Then the SQL access works fine:-

SELECT * FROM SQLSCHM.R1;
*+
*+ CALCKEY  SUB1  SUB2  SUB3  SUB4
*+ -------  ----  ----  ----  ----
*+       1  0001  0002  0003  0004
*+
*+ 1 row processed
SELECT CALCKEY, SUB1 FROM SQLSCHM.R1;
*+
*+ CALCKEY  SUB1
*+ -------  ----
*+       1  0001
*+
*+ 1 row processed
SELECT CALCKEY, SUB4 FROM SQLSCHM.R1;
*+
*+ CALCKEY  SUB4
*+ -------  ----
*+       1  0004
*+
*+ 1 row processed

Additional Information:

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

Accessing Network-Defined Databases

RECORD (REPORT/TRANSACTION) (for defining RECORD SYNONYMs).

Creating an RHDCOPTF Module