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
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.
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
For more information, see the following CA IDMS DocOps pages:
Accessing Network-Defined Databases
RECORD (REPORT/TRANSACTION) (for defining RECORD SYNONYMs).