Ian_Hill

CA Tuesday Tip: (CA IDMS) Multi-member sets in SQL

Discussion created by Ian_Hill Employee on Mar 12, 2013
Latest reply on Jun 11, 2013 by Eddy_G
CA IDMS Tuesday Tip by Cal Domingue, Principal Support Engineer for March 12, 2013.

If you are issuing an SQL SELECT against a network-defined database and a multi-member set is involved, it is necessary to qualify the set name with the record name (or alias).

For example, assuming that EMPSQL is an SQL schema defined for EMPSCHM, this will work:-

SET SESSION CURRENT SCHEMA EMPSQL;

SELECT H.HOSP_CITY_0430
FROM "HOSPITAL-CLAIM" H, COVERAGE C
WHERE "COVERAGE-CLAIMS".H AND C.TYPE_0400='M'
*+                        
*+ HOSP_CITY_0430         
*+ --------------         
*+ CHELSEA                
*+                        
*+ 1 row processed        

Note the ".H" which qualifies the COVERAGE-CLAIMS set with the HOSPITAL-CLAIM record.

Without it, you get a DB005526 error:-

SELECT H.HOSP_CITY_0430
FROM "HOSPITAL-CLAIM" H, COVERAGE C
WHERE "COVERAGE-CLAIMS" AND C.TYPE_0400='M'
*+ Status = -4       SQLSTATE = 42607        Messages follow:                
*+ DB005000 T145 C0M324: Message for module IDMSOCF, SQL statement number : 1.
*+ DB005526 T145 C-4M324: Multimember set COVERAGE-CLAIMS requires explicit  
*+ member name qualifier.                                                       

The documentation (SQL Reference manual, page 620 in the r18.0 version) implies that this is only necessary if more than one member record type of the multi-member set is referenced in the SELECT statement.

Outcomes