Verify that the number of records satisfies the condition in the QA database VOCABULARY_QA.
$ sqlplus DEV/myPass@DEV_VOCABExecute the following SQL commands: Concepts: SELECT *
FROM (SELECT VOCABULARY_ID,
VOCABULARY_NAME,
qc_VOCABULARY_ID_1,
qC_QA_REASON,
cnt_c,
CASE WHEN cnt_c > 0 THEN 'C' ELSE 'X' END cnt_c_
FROM (SELECT v.*,
qC.VOCABULARY_ID_1 qC_VOCABULARY_ID_1,
qC.QA_REASON qC_QA_REASON,
(SELECT COUNT (8)
FROM CONCEPT c
WHERE C.VOCABULARY_ID = v.VOCABULARY_ID)
cnt_c
FROM VOCABULARY v
LEFT JOIN VOCABULARY_QA qC
ON V.VOCABULARY_ID = qC.VOCABULARY_ID_1
AND qC.QA_REASON = 'C')
)
WHERE VOCABULARY_ID = NVL(:VocID, VOCABULARY_ID)
--/* -- MissVocID
AND (qC_QA_REASON <> cnt_c_)
--*/
ORDER BY VOCABULARY_ID; |
Relationships: SELECT *
FROM (SELECT VOCABULARY_ID,
VOCABULARY_NAME,
qR_VOCABULARY_ID_1,
qR_VOCABULARY_ID_2,
qR_QA_REASON,
cnt_r,
CASE WHEN cnt_r > 0 THEN 'R' ELSE 'X' END cnt_r_
FROM (SELECT v.*,
qR.VOCABULARY_ID_1 qR_VOCABULARY_ID_1,
qR.VOCABULARY_ID_2 qR_VOCABULARY_ID_2,
qR.QA_REASON qR_QA_REASON,
(SELECT COUNT (8)
FROM CONCEPT_RELATIONSHIP r, CONCEPT c1, CONCEPT c2
WHERE qR.VOCABULARY_ID_1 = C1.VOCABULARY_ID
AND c1.CONCEPT_ID = R.CONCEPT_ID_1
AND qR.VOCABULARY_ID_2 = C2.VOCABULARY_ID
AND c2.CONCEPT_ID = R.CONCEPT_ID_2)
cnt_r
FROM VOCABULARY v
LEFT JOIN VOCABULARY_QA qR
ON NOT (qR.VOCABULARY_ID_1 = 1 AND qR.VOCABULARY_ID_2 = 7)
AND V.VOCABULARY_ID = qR.VOCABULARY_ID_1
AND qR.QA_REASON = 'R'))
WHERE VOCABULARY_ID = NVL (:VocID, VOCABULARY_ID)
--/* -- MissVocID
AND ( qR_QA_REASON <> cnt_r_ )
--*/
ORDER BY VOCABULARY_ID; |
Ancestry: SELECT *
FROM (SELECT VOCABULARY_ID,
VOCABULARY_NAME,
qA_VOCABULARY_ID_1,
qA_VOCABULARY_ID_2,
qA_QA_REASON,
cnt_a,
CASE WHEN cnt_a > 0 THEN 'A' ELSE 'X' END cnt_a_
FROM (SELECT v.*,
qA.VOCABULARY_ID_1 qA_VOCABULARY_ID_1,
qA.VOCABULARY_ID_2 qA_VOCABULARY_ID_2,
qA.QA_REASON qA_QA_REASON,
(SELECT COUNT (8)
FROM CONCEPT_ANCESTOR a, CONCEPT c1, CONCEPT c2
WHERE qA.VOCABULARY_ID_1 = C1.VOCABULARY_ID
AND c1.CONCEPT_ID = a.ANCESTOR_CONCEPT_ID
AND qA.VOCABULARY_ID_2 = C2.VOCABULARY_ID
AND c2.CONCEPT_ID = a.DESCENDANT_CONCEPT_ID)
cnt_a
FROM VOCABULARY v
LEFT JOIN VOCABULARY_QA qA
ON V.VOCABULARY_ID = qA.VOCABULARY_ID_1
AND qA.QA_REASON = 'A'--AND V.VOCABULARY_ID IN (0)
))
WHERE VOCABULARY_ID = NVL (:VocID, VOCABULARY_ID)
--/* -- MissVocID
AND ( qA_QA_REASON <> cnt_a_ )
--*/
ORDER BY VOCABULARY_ID; |
Mappings: SELECT *
FROM (SELECT VOCABULARY_ID,
VOCABULARY_NAME,
qM_VOCABULARY_ID_1,
qM_VOCABULARY_ID_2,
qM_QA_REASON,
cnt_m,
CASE WHEN cnt_m > 0 THEN 'M' ELSE 'X' END cnt_m_
FROM (SELECT v.*,
qM.VOCABULARY_ID_1 qM_VOCABULARY_ID_1,
qM.VOCABULARY_ID_2 qM_VOCABULARY_ID_2,
qM.QA_REASON qM_QA_REASON,
(SELECT COUNT (8)
FROM SOURCE_TO_CONCEPT_MAP m, CONCEPT c2
WHERE qM.VOCABULARY_ID_1 = m.SOURCE_VOCABULARY_ID
AND qM.VOCABULARY_ID_2 = C2.VOCABULARY_ID
AND c2.CONCEPT_ID = m.TARGET_CONCEPT_ID)
cnt_m
FROM VOCABULARY v
LEFT JOIN VOCABULARY_QA qM
ON V.VOCABULARY_ID = qM.VOCABULARY_ID_1
AND qM.QA_REASON = 'M'))
WHERE VOCABULARY_ID = NVL (:VocID, VOCABULARY_ID)
--/* -- MissVocID
AND ( qM_QA_REASON <> cnt_m_ )
--*/
ORDER BY qM_VOCABULARY_ID_2, qM_VOCABULARY_ID_1 ; |
Note:
1. If the parameter :VocID is null then the verification will be for all the vocabularies
2. You can DEcomment line "--/* -- MissVocID" to show all the correct results |
