Distribution of Data Standards Check Tool v1.35_20230321
When using the standard check function based on the physical name, “'424' runtime error has occurred. An object is required.” There may be errors in the message. Distribute the data standard inspection tool v1.35_20230321 that corrected this error.
*(Reference) Last distribution article: Deploy Data Standards Check Tool v1.34_20221215
1. Phenomenon
If you enter a column name in column C (column name (physical name)) in the “Standard Check (Physical Name Standard)” sheet and click the Standard Check button, the following “'424' runtime error has occurred. An object is required.” An error occurs.
2. Cause
This error does not always occur, but occurs when each token does not exist in the physical name of the word dictionary when the physical name is divided by a delimiter (_, underscore).
Private Sub cmdRun_Click()
...
'------------------------------------------------------------------------------------------
'물리명 기준으로 논리명 찾기
Dim aColNamePart() As String, lColNamePartIdx As Long, oColNamePartAsStdWord As Collection
Dim oStdWordObj As Object, oStdWord As CStdWord, sLName As String
For lRow = LBound(vInRngArr) To UBound(vInRngArr)
sColName = vInRngArr(lRow, 1)
If Trim(sColName) = "" Then GoTo SkipBlank '점검할 속성명이 비어있는 경우 Skip
aColNamePart = Split(sColName, "_")
Set oColNamePartAsStdWord = Nothing
Set oColNamePartAsStdWord = New Collection
sLName = ""
For lColNamePartIdx = LBound(aColNamePart) To UBound(aColNamePart)
Set oStdWordObj = oStdWordDic.ItemP(aColNamePart(lColNamePartIdx)) '<-- 오류 발생
oColNamePartAsStdWord.Add oStdWordObj
If TypeOf oStdWordObj Is CStdWord Then
Set oStdWord = oStdWordObj
sLName = sLName + IIf(sLName = "", "", "_") + oStdWord.m_s단어논리명
End If
oOutRange.Offset(lRow - 1, 0).Value2 = sLName
Next lColNamePartIdx
SkipBlank:
Next lRow
...
End Sub
In line 15 of the code above, aColNamePart(lColNamePartIdx) means the nth value of tokens divided by the underscore character. An error occurs when this value does not exist in ItemP (a dictionary object that manages physical names as keys and word objects as values) of oStdWordDic (word dictionary object).
3. Measures
I changed the code as follows.
Private Sub cmdRun_Click()
...
'------------------------------------------------------------------------------------------
'물리명 기준으로 논리명 찾기
Dim aColNamePart() As String, lColNamePartIdx As Long, oColNamePartAsStdWord As Collection
Dim oStdWordObj As Object, oStdWord As CStdWord, sLName As String
Dim sTokenP As String, sTokenL As String
For lRow = LBound(vInRngArr) To UBound(vInRngArr)
sColName = vInRngArr(lRow, 1)
If Trim(sColName) = "" Then GoTo SkipBlank '점검할 속성명이 비어있는 경우 Skip
aColNamePart = Split(sColName, "_")
Set oColNamePartAsStdWord = Nothing
Set oColNamePartAsStdWord = New Collection
sLName = ""
For lColNamePartIdx = LBound(aColNamePart) To UBound(aColNamePart)
sTokenP = aColNamePart(lColNamePartIdx)
sTokenL = ""
If oStdWordDic.ExistsP(sTokenP) Then '단어 물리명이 사전에 있는 경우
Set oStdWordObj = oStdWordDic.ItemP(aColNamePart(lColNamePartIdx))
oColNamePartAsStdWord.Add oStdWordObj
If TypeOf oStdWordObj Is CStdWord Then
Set oStdWord = oStdWordObj
sTokenL = oStdWord.m_s단어논리명
End If
Else
'단어 물리명이 사전에 없는 경우
sTokenL = "[" + sTokenP + "]"
End If
sLName = sLName + IIf(sLName = "", "", "_") + sTokenL
oOutRange.Offset(lRow - 1, 0).Value2 = sLName
Next lColNamePartIdx
SkipBlank:
Next lRow
...
End Sub
If there is no physical name in the dictionary (line 18) and if it is not (line 25), there is no error, and if it is not in the dictionary, “[…] ]” form and made it stand out by conditional formatting.
If the physical name of a word does not exist in the dictionary, it is displayed as in the following example. This is an example of intentionally entering YMDX, ABC, and DEF that are not in the dictionary.
4. Download Data Standard Check Tool v1.35_20230321
The patch version has been uploaded to github and can be downloaded from the URL below.
https://github.com/DAToolset/ToolsForDataStandard/raw/main/속성%20표준점검%20도구_v1.35_20230321_1.xlsm
Related articles:
Deploy Data Standards Check Tool v1.34_20221215
Data Standard Check Tool Description Contents , Download
Data standard check tool_4.Attachment
Data Standard Check Tool_3. Standard Check Case Results
Data standard inspection tool_2.3. Data standard dictionary composition
Data standard inspection tool_1.Overview
DA# Macro(2): DA# Macro Function(1)-Common Function, Entity Get/Set
DA# Macro(1): DA#, DA# API, DA# Macro Overview
English 
한국어 
简体中文 
日本語 
Deutsch 
Español
Standard dictionary query for standard dictionary Repository related Config.
Repository DB: PostgreSQL, standardized DB: SQL Server
– Query standard word dictionary
SELECT ROW_NUMBER() OVER (ORDER BY D.DIC_LOG_NM) AS NO
,D.DIC_LOG_NM AS “word logic name”
,D.DIC_PHY_NM AS “word physical name”
,D.DIC_PHY_FLL_NM AS “English word name”
,D.DIC_DESC AS “word description”
,D.STANDARD_YN AS “Standard”
,(case D.ATTR_CLSS_YN when 'Y' then 'Y' else 'N' END) AS “whether attribute classifier exists”
,CASE
WHEN D.STANDARD_YN = 'Y' AND SIM.Standard logic name IS NULL THEN D.DIC_LOG_NM
WHEN D.STANDARD_YN = 'Y' AND SIM.standard logic name IS NOT NULL THEN SIM.standard logic name
ELSE SIM. standard logic name
END “standard logic name”
,SIM. synonym
,G.DOM_GRP_NM AS “Domain Classification Name”
/*
,D.ENT_CLSS_YN AS “Entity classifier
*/
FROM dataware.STD_AREA A
,dataware.STD_DIC D
left outer join
(SELECT DM.KEY_DOM_NM
,MAX(DM.DOM_GRP_ID) DOM_GRP_ID
,MAX(DG.DOM_GRP_NM) DOM_GRP_NM
FROM dataware.STD_DOM DM
,dataware.STD_DOM_GRP DG
WHERE DM.STD_AREA_ID = 'Need to check key value!!!'
AND DM.AVAL_END_DT = '99991231235959'
AND DG.STD_AREA_ID = DM.STD_AREA_ID
AND DG.AVAL_END_DT = '99991231235959'
AND DM.DOM_GRP_ID = DG.DOM_GRP_ID
GROUP BY DM.KEY_DOM_NM) G
on D.DIC_LOG_NM = G.KEY_DOM_NM
left outer join
(
— if standard
SELECT A.P_DIC_ID “Group ID”
,A.P_DIC_ID DIC_ID
,P.DIC_LOG_NM “standard logic name”
,STRING_AGG(C.DIC_LOG_NM, ',' ORDER BY A.C_DIC_ID) “Synonym”
FROM dataware.STD_DIC_REL A
left outer join dataware.STD_DIC P
on A.P_DIC_ID = P.DIC_ID AND P.AVAL_END_DT = '99991231235959'
left outer join dataware.STD_DIC C
on A.C_DIC_ID = C.DIC_ID and C.AVAL_END_DT = '99991231235959'
WHERE 1=1
AND A.P_STD_AREA_ID = (SELECT STD_AREA_ID FROM DATAWARE.STD_AREA WHERE AVAL_END_DT = '99991231235959' AND STD_AREA_NM = 'Standard Dictionary')
AND A.AVAL_END_DT = '99991231235959'
GROUP BY A.P_DIC_ID, P.DIC_LOG_NM
UNION ALL
— if non-standard
SELECT AA.P_DIC_ID “Group ID”
,A.C_DIC_ID DIC_ID
,P.DIC_LOG_NM “standard logic name”
,STRING_AGG(CASE WHEN AA.C_DIC_ID = A.C_DIC_ID THEN P.DIC_LOG_NM ELSE C.DIC_LOG_NM END, ',' ORDER by CASE WHEN AA.C_DIC_ID = A.C_DIC_ID THEN '0' ELSE AA.C_DIC_ID END) “Synonym”
FROM dataware.STD_DIC_REL A
,dataware.STD_DIC_REL AA
,dataware.STD_DIC P
,dataware.STD_DIC C
WHERE 1=1
AND A.P_STD_AREA_ID = (SELECT STD_AREA_ID FROM DATAWARE.STD_AREA WHERE AVAL_END_DT = '99991231235959' AND STD_AREA_NM = 'Standard Dictionary')
AND A.AVAL_END_DT = '99991231235959'
AND A.P_DIC_ID = AA.P_DIC_ID
AND AA.P_DIC_ID = P.DIC_ID
AND P.AVAL_END_DT = '99991231235959'
AND AA.C_DIC_ID = C.DIC_ID
AND C.AVAL_END_DT = '99991231235959'
GROUP BY AA.P_DIC_ID, A.C_DIC_ID, P.DIC_LOG_NM
) SIM
on D.DIC_ID = SIM.DIC_ID
WHERE A.STD_AREA_NM = 'Standard Dictionary'
AND A.STD_AREA_ID = D.STD_AREA_ID
AND D.AVAL_END_DT = '99991231235959'
AND D.DIC_GBN_CD = '0001' /* '0001': word */
/* AND D.STANDARD_YN = 'Y' extract standard words only */
ORDER BY D.DIC_LOG_NM, (case D.STANDARD_YN when 'Y' then 1 else 2 end)
;
– Query standard term dictionary
SELECT ROW_NUMBER() OVER (ORDER BY D.DIC_LOG_NM) AS NO
,D.DIC_LOG_NM AS “term logic name”
,
(
SELECT STRING_AGG(X.DIC_LOG_NM, '_')
FROM
(
SELECT WRD.DIC_LOG_NM
FROM STD_DIC AS WRD
LEFT OUTER JOIN STD_WORD_COMBI AS C
ON WRD.STD_AREA_ID = C.STD_AREA_ID
AND WRD.DIC_ID = C.WORD_ID
WHERE C.STD_AREA_ID = (SELECT STD_AREA_ID FROM DATAWARE.STD_AREA WHERE AVAL_END_DT = '99991231235959' AND STD_AREA_NM = 'Standard Dictionary')
AND C.TERM_ID = D.DIC_ID
AND C.AVAL_END_DT = '99991231235959'
ORDER BY C.ORDER_NO
) AS X
) AS “word logical name combination”
,D.DIC_PHY_NM AS “Term Physical Name”
,D.DIC_DESC AS “Glossary”
,DM.DOM_NM AS “Domain Logical Name”
,UPPER(DT.DATA_TYPE) AS “datatype”
— ,DM.DATA_LEN AS “length”
,(case when DM.DATA_LEN = -1 then 'MAX' else text(DM.DATA_LEN) END) AS “length”
,DM.DATA_SCALE AS “degree”
,” as “task definition”
,UPPER(DT.DATA_TYPE)
||case
when DM.DATA_LEN is null and UPPER(DT.DATA_TYPE) not in ('INT', 'DATETIME2') then null
when DM.DATA_LEN is null and UPPER(DT.DATA_TYPE) in ('INT', 'DATETIME2') then ”
WHEN DM.DATA_LEN = -1 AND DM.DATA_SCALE IS NULL and UPPER(DT.DATA_TYPE) in ('VARCHAR', 'VARBINARY') THEN '(MAX'
else '('||DM.DATA_LEN
end
||CASE WHEN DM.DATA_LEN IS NOT NULL AND DM.DATA_SCALE IS NULL THEN ')'
WHEN DM.DATA_LEN IS NOT NULL and DM.DATA_LEN > 0 AND DM.DATA_SCALE IS NOT NULL THEN ','||DM.DATA_SCALE||')'
WHEN DM.DATA_LEN IS NULL AND DM.DATA_SCALE IS NULL and UPPER(DT.DATA_TYPE) in ('INT', 'DATETIME2') THEN ”
ELSE NULL end
AS “TypeSize”
FROM dataware.STD_AREA A
,dataware.STD_DIC D
,dataware.STD_DOM DM
,dataware.STD_DATATYPE DT
WHERE A.STD_AREA_NM = 'Standard Dictionary'
AND A.STD_AREA_ID = D.STD_AREA_ID
AND D.AVAL_END_DT = '99991231235959'
AND D.DIC_GBN_CD = '0002' /* '0002': term */
AND D.STANDARD_YN = 'Y' /* Extract standard terms only */
AND DM.STD_AREA_ID = D.STD_AREA_ID
AND DM.AVAL_END_DT = '99991231235959'
AND DM.DOM_ID = D.DOM_ID
AND DT.DB_TYPE = '0006' –SQL SERVER
AND DT.CD_NO = DM.DATA_TYPE_CD
;
–Standard domain dictionary query
SELECT ROW_NUMBER() OVER (ORDER BY DG.DOM_GRP_NM, DM.DOM_NM) AS NO
,DG.DOM_GRP_NM AS “Domain Classification Name”
,DM.DOM_NM AS “Domain Logical Name”
,DM.KEY_DOM_PHY_NM AS “Key Domain Physic Name”
,(case DM.DOM_DESC when null then DM.KEY_DOM_NM else DM.DOM_DESC END) AS “Domain Description”
,UPPER(DT.DATA_TYPE) AS “data type name”
,(case when DM.DATA_LEN = -1 then 'MAX' else text(DM.DATA_LEN) END) AS “length”
,DM.DATA_SCALE AS “degree”
,UPPER(DT.DATA_TYPE)
||case
when DM.DATA_LEN is null and UPPER(DT.DATA_TYPE) not in ('INT', 'DATETIME2') then null
when DM.DATA_LEN is null and UPPER(DT.DATA_TYPE) in ('INT', 'DATETIME2') then ”
WHEN DM.DATA_LEN = -1 AND DM.DATA_SCALE IS NULL and UPPER(DT.DATA_TYPE) in ('VARCHAR', 'VARBINARY') THEN '(MAX'
else '('||DM.DATA_LEN
end
||CASE WHEN DM.DATA_LEN IS NOT NULL AND DM.DATA_SCALE IS NULL THEN ')'
WHEN DM.DATA_LEN IS NOT NULL and DM.DATA_LEN > 0 AND DM.DATA_SCALE IS NOT NULL THEN ','||DM.DATA_SCALE||')'
WHEN DM.DATA_LEN IS NULL AND DM.DATA_SCALE IS NULL and UPPER(DT.DATA_TYPE) in ('INT', 'DATETIME2') THEN ”
ELSE NULL end
AS “TypeSize”
/*
,DM.KEY_DOM_NM AS “key domain name”
*/
FROM dataware.STD_DOM DM
,dataware.STD_DOM_GRP DG
,dataware.STD_DATATYPE DT
WHERE DM.STD_AREA_ID = (SELECT STD_AREA_ID FROM DATAWARE.STD_AREA WHERE AVAL_END_DT = '99991231235959' AND STD_AREA_NM = 'Standard Dictionary')
AND DM.AVAL_END_DT = '99991231235959'
AND DG.STD_AREA_ID = DM.STD_AREA_ID
AND DG.AVAL_END_DT = '99991231235959'
AND DG.DOM_GRP_ID = DM.DOM_GRP_ID
AND DT.DB_TYPE = '0006'
AND DT.CD_NO = DM.DATA_TYPE_CD
;
Thanks for sharing the standard dictionary lookup SQL.
I will also use it when using the PostgreSQL version.
White space indentation is not displayed in comments.
Let's find a way.
When checking the standard, if the property name (CUSTOMER NUMBER) is combined with only English words, there is a symptom that the standard word combination and standard English matching cannot be checked even if the standard term is registered. I wonder if it has something to do with the logic that treats delimiters as underscores and spaces?
EX)
*Standard words, abbreviations
CUSTOMERS, CUSTOMERS
NUMBER, NO
*Standard term, domain
CUSTOMER NUMBER, NUMBER V10
*Standard domain, data type
NUMBER V10, VARCHAR(10)
https://prodskill.com/ko/data-standard-checker-4-case-study/#33_%ED%91%9C%EC%A4%80%EC%A0%90%EA%B2%80_%EC%98%88%EC%8B%9C_Case_2_%ED%91%9C%EC%A4%80%EB%8B%A8%EC%96%B4_%EC%A1%B0%ED%95%A9
This article contains the following:
—————————————————————
The attribute “whether it is a private object” (Row# 9) contains a blank. In the “Standard Word Logical Name Combination” item, “[personal target]_whether” is displayed means that “[personal target]” is not in the standard word dictionary and “whether” is in the standard word dictionary. This cell also contains unregistered characters, so the background color is set to red. If a separator is included in the property name, it is displayed as “(user designation)” in the “Property Name Check Result” item.
—————————————————————
Space characters in the property names subject to standard inspection are intentionally used to separate words.
With the currently implemented function, if blanks are allowed in the logical name of the standard term, matching is not checked based on the standard term.
It seems that the inspection you want can be done by creating an option to select whether to treat the blanks entered in the property name to be checked as “intentional word separators” or “space characters” themselves.
do you need it ^^
In the case of modeling in English, it is difficult to identify words if the property name is attached like a Korean word, so it seems that an option is needed if you need to separate English words with a space.. ^^;;
https://github.com/DAToolset/ToolsForDataStandard/raw/main/%EC%86%8D%EC%84%B1%20%ED%91%9C%EC%A4%80%EC%A0%90%EA%B2%80%20%EB%8F%84%EA%B5%AC_v1.36_20230426_1.xlsm
In v1.36, it was changed so that blank characters in property names subject to standard inspection are not treated as word separators.
Accordingly, the rules for creating the property name to be inspected have been changed as follows.
※ Attribute name creation rules
1. Enter without spaces between words: default
– Example: Whether or not to automatically run the task
2. Enter space between words: When writing the property logic name in English
– Example: CUSTOMER NUMBER
3. Enter separated by “_”: When specifying a word structure
– Example: whether the job_automatically_executed_or not
Please test it out and comment.
Property name: CUSTOMER SPLIT NUMBER
During standard inspection, the property name inspection result is displayed as a standard word combination, but the results of standard word logical name combination and physical name combination are incorrectly processed.
CUSTOMER, SPLIT, NUMBER: 3 standard words registered
Standard word logical name combination: CUSTOMER_[ ]_SPLIT_[ ]_NUMBER
Standard word physical name combination: CUST_[ ]_SPLT_[ ]_NO
I saw it so simply ^^;;
I'll do some more testing and redeploy.
v1.36 Redeployed.
https://prodskill.com/ko/data-standard-checker-v1-36/
Repository DB: PostgreSQL, Standardization target DB: PostgreSQL
SELECT ROW_NUMBER() OVER (ORDER BY D.DIC_LOG_NM) AS NO
,D.DIC_LOG_NM AS “word logic name”
,D.DIC_PHY_NM AS “word physical name”
,D.DIC_PHY_FLL_NM AS “English word name”
,D.DIC_DESC AS “word description”
,D.STANDARD_YN AS “Standard”
,(case D.ATTR_CLSS_YN when 'Y' then 'Y' else 'N' END) AS “whether attribute classifier exists”
,CASE
WHEN D.STANDARD_YN = 'Y' AND SIM.Standard logic name IS NULL THEN D.DIC_LOG_NM
WHEN D.STANDARD_YN = 'Y' AND SIM.standard logic name IS NOT NULL THEN SIM.standard logic name
ELSE SIM. standard logic name
END “standard logic name”
,SIM. synonym
,G.KEY_DOM_NM AS “domain classification name”
/*
,D.ENT_CLSS_YN AS “Entity classifier
*/
FROM dataware.STD_AREA A
,dataware.STD_DIC D
left outer join
(SELECT DM.KEY_DOM_NM
,MAX(DM.DOM_GRP_ID) DOM_GRP_ID
,MAX(DG.DOM_GRP_NM) DOM_GRP_NM
FROM dataware.STD_DOM DM
,dataware.STD_DOM_GRP DG
WHERE DM.STD_AREA_ID = '47430eed-15a9-4697-90e3-a70040266517'
AND DM.AVAL_END_DT = '99991231235959'
AND DG.STD_AREA_ID = DM.STD_AREA_ID
AND DG.AVAL_END_DT = '99991231235959'
AND DM.DOM_GRP_ID = DG.DOM_GRP_ID
GROUP BY DM.KEY_DOM_NM) G
on D.DIC_LOG_NM = G.KEY_DOM_NM
left outer join
(
-If it is standard
SELECT A.P_DIC_ID “Group ID”
,A.P_DIC_ID DIC_ID
,P.DIC_LOG_NM “standard logic name”
,STRING_AGG(C.DIC_LOG_NM, ',' ORDER BY A.C_DIC_ID) “Synonym”
FROM dataware.STD_DIC_REL A
left outer join dataware.STD_DIC P
on A.P_DIC_ID = P.DIC_ID AND P.AVAL_END_DT = '99991231235959'
left outer join dataware.STD_DIC C
on A.C_DIC_ID = C.DIC_ID and C.AVAL_END_DT = '99991231235959'
WHERE 1=1
AND A.P_STD_AREA_ID = (SELECT STD_AREA_ID FROM DATAWARE.STD_AREA WHERE AVAL_END_DT = '99991231235959' AND STD_AREA_NM = '0.Standard Dictionary')
AND A.AVAL_END_DT = '99991231235959'
GROUP BY A.P_DIC_ID, P.DIC_LOG_NM
UNION ALL
-If non-standard
SELECT AA.P_DIC_ID “Group ID”
,A.C_DIC_ID DIC_ID
,P.DIC_LOG_NM “standard logic name”
,STRING_AGG(CASE WHEN AA.C_DIC_ID = A.C_DIC_ID THEN P.DIC_LOG_NM ELSE C.DIC_LOG_NM END, ',' ORDER by CASE WHEN AA.C_DIC_ID = A.C_DIC_ID THEN '0' ELSE AA.C_DIC_ID END) “Synonym”
FROM dataware.STD_DIC_REL A
,dataware.STD_DIC_REL AA
,dataware.STD_DIC P
,dataware.STD_DIC C
WHERE 1=1
AND A.P_STD_AREA_ID = (SELECT STD_AREA_ID FROM DATAWARE.STD_AREA WHERE AVAL_END_DT = '99991231235959' AND STD_AREA_NM = '0.Standard Dictionary')
AND A.AVAL_END_DT = '99991231235959'
AND A.P_DIC_ID = AA.P_DIC_ID
AND AA.P_DIC_ID = P.DIC_ID
AND P.AVAL_END_DT = '99991231235959'
AND AA.C_DIC_ID = C.DIC_ID
AND C.AVAL_END_DT = '99991231235959'
GROUP BY AA.P_DIC_ID, A.C_DIC_ID, P.DIC_LOG_NM
) SIM
on D.DIC_ID = SIM.DIC_ID
WHERE A.STD_AREA_NM = '0.Standard Dictionary'
AND A.STD_AREA_ID = D.STD_AREA_ID
AND D.AVAL_END_DT = '99991231235959'
AND D.DIC_GBN_CD = '0001' /* '0001': word */
/* AND D.STANDARD_YN = 'Y' extract standard words only */
ORDER BY D.DIC_LOG_NM, (case D.STANDARD_YN when 'Y' then 1 else 2 end)
;
– Query standard term dictionary
SELECT ROW_NUMBER() OVER (ORDER BY D.DIC_LOG_NM) AS NO
,D.DIC_LOG_NM AS “term logic name”
,
(
SELECT STRING_AGG(X.DIC_LOG_NM, '_')
FROM
(
SELECT WRD.DIC_LOG_NM
FROM STD_DIC AS WRD
LEFT OUTER JOIN STD_WORD_COMBI AS C
ON WRD.STD_AREA_ID = C.STD_AREA_ID
AND WRD.DIC_ID = C.WORD_ID
WHERE C.STD_AREA_ID = (SELECT STD_AREA_ID FROM DATAWARE.STD_AREA WHERE AVAL_END_DT = '99991231235959' AND STD_AREA_NM = '0.Standard Dictionary')
AND C.TERM_ID = D.DIC_ID
AND C.AVAL_END_DT = '99991231235959'
AND WRD.AVAL_END_DT = '99991231235959'
ORDER BY C.ORDER_NO
) AS X
) AS “word logical name combination”
,D.DIC_PHY_NM AS “Term Physical Name”
,D.DIC_DESC AS “Glossary”
,DM.DOM_NM AS “Domain Logical Name”
,UPPER(DT.DATA_TYPE) AS “datatype”
— ,DM.DATA_LEN AS “length”
,(case when DM.DATA_LEN = -1 then 'MAX' else text(DM.DATA_LEN) END) AS “length”
,DM.DATA_SCALE AS “degree”
,” as “task definition”
, replace(
UPPER(DT.DATA_TYPE)
||'(‘
|| case when DM.DATA_LEN is null then ” else cast(DM.DATA_LEN as text) end
|| case when DM.DATA_SCALE is null then ” else cast(DM.DATA_SCALE as text) end
||’)’
, '()', ”) AS “TypeSize”
FROM dataware.STD_AREA A
,dataware.STD_DIC D
,dataware.STD_DOM DM
,dataware.STD_DATATYPE DT
WHERE A.STD_AREA_NM = '0.Standard Dictionary'
AND A.STD_AREA_ID = D.STD_AREA_ID
AND D.AVAL_END_DT = '99991231235959'
AND D.DIC_GBN_CD = '0002' /* '0002': term */
AND D.STANDARD_YN = 'Y' /* Extract standard terms only */
AND DM.STD_AREA_ID = D.STD_AREA_ID
AND DM.AVAL_END_DT = '99991231235959'
AND DM.DOM_ID = D.DOM_ID
AND DT.DB_TYPE = '0024'
AND DT.CD_NO = DM.DATA_TYPE_CD
;
–Standard domain dictionary query
SELECT ROW_NUMBER() OVER (ORDER BY DG.DOM_GRP_NM, DM.DOM_NM) AS NO
,DM.key_dom_nm AS “domain classification name”
,DM.DOM_NM AS “Domain Logical Name”
,DM.KEY_DOM_PHY_NM AS “Key Domain Physic Name”
,(case DM.DOM_DESC when null then DM.KEY_DOM_NM else DM.DOM_DESC END) AS “Domain Description”
,UPPER(DT.DATA_TYPE) AS “data type name”
,DM.DATA_LEN AS “length”
,DM.DATA_SCALE AS “degree”
, replace(
UPPER(DT.DATA_TYPE)
||'(‘
|| case when DM.DATA_LEN is null then ” else cast(DM.DATA_LEN as text) end
|| case when DM.DATA_SCALE is null then ” else cast(DM.DATA_SCALE as text) end
||’)’
, '()', ”) AS “TypeSize”
, dg.dom_grp_nm as “Standard domain group name”
/*
,DM.KEY_DOM_NM AS “key domain name”
*/
FROM dataware.STD_DOM DM
,dataware.STD_DOM_GRP DG
,dataware.STD_DATATYPE DT
WHERE DM.STD_AREA_ID = (SELECT STD_AREA_ID FROM DATAWARE.STD_AREA WHERE AVAL_END_DT = '99991231235959' AND STD_AREA_NM = '0.Standard Dictionary')
AND DM.AVAL_END_DT = '99991231235959'
AND DG.STD_AREA_ID = DM.STD_AREA_ID
AND DG.AVAL_END_DT = '99991231235959'
AND DG.DOM_GRP_ID = DM.DOM_GRP_ID
AND DT.DB_TYPE = '0024'
AND DT.CD_NO = DM.DATA_TYPE_CD
;
Thanks for sharing.