The table definition column data glossary query returns the names of Shareable Table Definitions which contain a column which matches the data characteristics specified in the WHERE clause.
A column in a Shareable Table Definitions may use a Shareable Data Element to specify its data characteristics, the characteristics may be specified locally, or it may inherit the data characteristics of the matching column in a parent table if the column participates in a foreign key.
In order to locate all Shareable Table Definitions which contain a column with certain characteristics, the query must include the attributes which define the column using a Shareable Data Element, defined locally or via the parent table.
View type: DAQTableDefinition
Modifications:
The query uses a SQL UNION clause to provide a more compact result table.
Change the search criteria of the data characteristic attributes in the SQL WHERE clause for each subselect.
Each of the following attributes may be referenced either as shareable or local:
--typeCode
Possible values are:
0 Fixed binary
1 Packed decimal
2 Zoned decimal
3 Binary floating point
4 Decimal (or external) floating point
5 Bit string
6 Character string
7 DBCS (or graphic) string
8 Mixed SBCS/DBCS string
9 Date
10 Time
11 Timestamp
12 Index
13 Undefined
14 Rowid (Oracle)
15 MLSLable (Oracle)
--stringLength
The data length for string data types: Bit string in bits, Character
string in bytes, DBCS or graphic string in double bytes, and mixed
SBCS/DBCS string in bytes.
--numericPrecision
The data length for numeric data types: Fixed binary in bits, Packed
decimal in digits, zoned decimal in digits, binary floating point in
bits, and decimal floating point in digits of the mantissa.
--scale
The position of the decimal point for numeric data types in digits.
--asBitData
A boolean value indicating whether the character string is treated
as bit (binary) data.
--stringVaryingCode
A value indicating whether the string is of fixed length (1) or
variable length (2).
#MODIFIED#
#VERSION#
#SQL STATEMENT#
SELECT
-- column uses Shareable Data Element
t.tableDefinition,
t.columnName,
'Data Element' as sourceType,
e.dataElement as dataSource,
e.typeCode as typeCode,
e.stringLength as stringLength,
e.stringVaryingCode as stringVaryingCode,
e.asBitData as asBitData,
e.isLOB as isLOB,
e.numericPrecision as numericPrecision,
e.scale as numericScale
FROM
DAQTableDefinition t,
(t.usesDataElement) e
WHERE
e.typeCode=6 AND
e.stringLength<20 AND
t.columnName <> ''
UNION
SELECT
-- column is defined by primary table column
-- primary table column uses a Shareable Data Element