Prerequisite‎ > ‎

Verification Script

Verify that the number of records satisfies the condition in the QA database VOCABULARY_QA.

sqlplus  DEV/myPass@DEV_VOCAB

Execute 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

Comments