This query collects information about fields in IMS DBDs.
Modifications:
Change the search criteria of the dbd name in the SQL WHERE clause to get fields in one DBD.
Use
WHERE d.segmName = "<DBD1>SEG1<var:rev>" to find fields in the segment named SEG1 in DBD named DBD1.
Note:
There are four possible paths to find the data characteristics of the field, depending on whether DSField uses a shared DSDataItem or a local DSDataItem, and on whether the DSDataItem uses a DSSimpleTypeDef directly, or goes through a shareable Data
Element.
d.typeCode will contain the data type code for a field using a local dataItem with a simpleTypeDef
se.typeCode will contain the data type code when the local dataItem has a shareable DataElement.
ms.typeCode will contain the data type code when the shared dataItem has a simpleTypeDef.
me.typeCode will contain the data type code when the shared dataItem has a shareable DataElement.
View type: DAQmsdbDBD
#MODIFIED#
#VERSION#
#SQL STATEMENT#
SELECT DISTINCT
d.fieldName,
se.dataElement as elementName,
d.sequenceField,
d.uniqueSequence,
d.offsetFromLevel01,
d.typeCode as "type",
d.asBitData as "isBitData",
d.numericPrecision as "precision",
d.strLength as "length",
d.fieldLabel,
d.fieldDescription
FROM OUTER
DAQmsdbDBD d,
(d.pSharedElement) se
WHERE
-- also change the DBD name in the next WHERE clauses
d.dbdName LIKE '<>%'
UNION
SELECT DISTINCT
d.fieldName,
se.dataElement as elementName,
d.sequenceField,
d.uniqueSequence,
d.offsetFromLevel01,
se.typeCode as "type",
se.asBitData as "isBitData",
se.numericPrecision as "precision",
se.stringLength as "length",
d.fieldLabel,
d.fieldDescription
FROM OUTER
DAQmsdbDBD d,
(d.pSharedElement) se
WHERE
-- also change the DBD name in the other WHERE clauses
d.dbdName LIKE '<>%'
AND
d.pSharedElement IS NOT NULL
UNION
SELECT DISTINCT
d.fieldName,
me.elementName as elementName,
d.sequenceField,
d.uniqueSequence,
d.offsetFromLevel01,
me.typeCode as "type",
me.asBitData as "isBitData",
me.numericPrecision as "precision",
me.stringLength as "length",
d.fieldLabel,
d.fieldDescription
FROM OUTER
DAQmsdbDBD d,
(d.mappingStruct) ms,
(ms.usesDataElement) me,
(ms.imsFieldName) dea
WHERE
-- also change the DBD name in the other WHERE clauses
d.dbdName LIKE '<>%'
AND
d.fieldName = dea.elementAlias
AND
d.mappingStruct IS NOT NULL
AND
ms.usesDataElement IS NOT NULL
UNION
SELECT DISTINCT
d.fieldName,
me.elementName as elementName,
d.sequenceField,
d.uniqueSequence,
d.offsetFromLevel01,
ms.dataTypeCode as "type",
ms.asBitData as "isBitData",
ms."precision" as "precision",
ms.stringLength as "length",
d.fieldLabel,
d.fieldDescription
FROM OUTER
DAQmsdbDBD d,
(d.mappingStruct) ms,
(ms.usesDataElement) me,
(ms.imsFieldName) dea
WHERE
-- also change the DBD name in the previous WHERE clauses