This document describes how to use the new VIRTUAL FOREIGN KEYS feature of CA-IDMS r19.0 to perform a LEFT JOIN on a network-defined database with more than two records involved.
Historically, performing a LEFT JOIN on a network-defined database with more than two tables involved in the SELECT has been problematic.
Either it would return an error or an unexpected result.
The addition of Incremental Release 2 (with PTF RO80871) on CA-IDMS r19.0 has made this possible.
The following examples use schema EMPNET with this definition and having it set as the CURRENT SCHEMA:
CREATE SCHEMA EMPNET
FOR NONSQL SCHEMA APPLDICT.EMPSCHM VERSION 100
DBNAME EMPDEMO
;
SET SESSION CURRENT SCHEMA EMPNET;
*+ Status = 0 SQLSTATE = 00000
With two tables, a LEFT JOIN works either with the new JOIN syntax:
SELECT E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM EMPLOYEE E
LEFT JOIN EXPERTISE X ON "EMP-EXPERTISE"
WHERE EMP_ID_0415 BETWEEN 125 AND 150;
*+
*+ EMP_ID_0415 SKILL_LEVEL_0425
*+ ----------- ----------------
*+ 127 04
*+ 127 03
*+ 149 <null>
*+
*+ 3 rows processed
or with the old PRESERVE syntax:
SELECT E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM EMPLOYEE E, EXPERTISE X
WHERE "EMP-EXPERTISE" AND EMP_ID_0415 BETWEEN 125 AND 150
PRESERVE EMPLOYEE;
*+
*+ EMP_ID_0415 SKILL_LEVEL_0425
*+ ----------- ----------------
*+ 127 04
*+ 127 03
*+ 149 <null>
*+
*+ 3 rows processed
However, if you want to perform a LEFT JOIN in a SELECT involving more than two tables, it’s not as simple.
The new JOIN syntax gives an error:
SELECT D.DEPT_ID_0410, E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM DEPARTMENT D
INNER JOIN EMPLOYEE E ON "DEPT-EMPLOYEE"
LEFT JOIN EXPERTISE X ON "EMP-EXPERTISE"
WHERE DEPT_ID_0410=4000;
*+ Status = -4 SQLSTATE = 5000B Messages follow:
*+ DB005000 T90 C0M324: Message for module IDMSOCF, SQL statement number : 1.
*+ DB005507 T90 C-4M324: Set name EMP-EXPERTISE reference incompatible with statement
The older PRESERVE syntax works but does not give the expected result. It returns NULLs in the fields from the higher-level record as well as the lower-level record:
SELECT D.DEPT_ID_0410, E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM DEPARTMENT D, EMPLOYEE E, EXPERTISE X
WHERE "DEPT-EMPLOYEE" AND "EMP-EXPERTISE" AND DEPT_ID_0410=4000
PRESERVE EMPLOYEE;
*+
*+ DEPT_ID_0410 EMP_ID_0415 SKILL_LEVEL_0425
*+ ------------ ----------- ----------------
*+ <null> 23 <null>
…
*+ <null> 479 <null>
*+ 4000 127 04
*+ 4000 127 03
*+ <null> 472 <null>
*+ <null> 45 <null>
*+ <null> 91 <null>
*+ <null> 28 <null>
*+ <null> 3 <null>
*+ <null> 1 <null>
*+ 4000 120 04
*+ 4000 120 04
*+ 4000 120 03
*+ <null> 69 <null>
…
*+ <null> 106 <null>
*+ 4000 7 04
*+ <null> 334 <null>
…
*+ <null> 74 <null>
*+ 4000 119 04
*+ 4000 119 04
*+ <null> 35 <null>
…
*+ <null> 301 <null>
*+
*+ 60 rows processed
As of r19.0 Incremental Release 2, provided by PTF RO80871, VIRTUAL FOREIGN KEY support has been added.
Among other things, this removes the need to use SET names to join records.
With the following schema defined and made current:
CREATE SCHEMA EMPNETV
FOR NONSQL SCHEMA APPLDICT.EMPSCHM VERSION 100
DBNAME EMPDEMO
WITH VIRTUAL KEYS
;
SET SESSION CURRENT SCHEMA EMPNETV;
*+ Status = 0 SQLSTATE = 00000
You can issue the following SELECT to get the desired result:
SELECT D.DEPT_ID_0410, E.EMP_ID_0415, X.SKILL_LEVEL_0425
FROM DEPARTMENT D
INNER JOIN EMPLOYEE E ON E.FKEY_DEPT_EMPLOYEE=D.ROWID
LEFT JOIN EXPERTISE X ON X.FKEY_EMP_EXPERTISE=E.ROWID
WHERE DEPT_ID_0410=4000;
*+
*+ DEPT_ID_0410 EMP_ID_0415 SKILL_LEVEL_0425
*+ ------------ ----------- ----------------
*+ 4000 120 04
*+ 4000 120 03
*+ 4000 120 04
*+ 4000 7 04
*+ 4000 119 04
*+ 4000 119 04
*+ 4000 158 <null>
*+ 4000 127 04
*+ 4000 127 03
*+ 4000 149 <null>
*+ 4000 476 <null>
*+
*+ 11 rows processed
Note, the old PRESERVE syntax with the VIRTUAL FOREIGN KEYS support works but gives the same unexpected result mentioned above.