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 |